MySQL GroupBy and Shows it horizontally

Question

Supposed that I have table below:

1) tblScore

============================ 
Date      VendorID     Score 
============================ 
12/09/01  12001        A     
12/09/01  12001        A     
12/09/01  12002        B     
12/09/02  12003        C     
12/09/02  12003        A     
12/09/03  12001        C     
============================

I have this query:

SELECT ts.VendorID, ts.Score, COUNT(*)
FROM trxscore ts
GROUP BY ts.VendorID, ts.Score
ORDER BY ts.VendorID, ts.Score

But how to show the table like:

===========================
VendorID    A    B    C
===========================
12001       2    0    1
12002       0    1    0 
12003       1    0    1
===========================

And, is it possible to get an average from a text? i.e., VendorID 12001 should get the average of A. Thanks...

I don't understand the way you calculate the avg. Can you explain more ? — swingNoobie, Oct 01 '12 at 02:31

score 8 · Accepted Answer · edited May 07 '20 at 11:58

8

Try this,

SELECT  VendorID,
        SUM(CASE WHEN Score = 'A' THEN 1 ELSE 0 END) totalA,
        SUM(CASE WHEN Score = 'B' THEN 1 ELSE 0 END) totalB,
        SUM(CASE WHEN Score = 'C' THEN 1 ELSE 0 END) totalC
FROM    tableName
GROUP BY VendorID

SQLFiddle Demo

edited May 07 '20 at 11:58

A Friend

1,420
17
22

answered Oct 01 '12 at 02:18

John Woo

258,903
69
498
492

Thanks, it works great! :) .. Btw, is it possible to get the average from that score? – mrjimoy_05 Oct 01 '12 at 02:26
you are welcome, btw, what do you by average? for example, `12001` should get `1`? – John Woo Oct 01 '12 at 02:31
No, it means: When `12001` get score: A, A, A, B, C, C, then the average should be `A`. It's not average, but the maximum value, sorry.. – mrjimoy_05 Oct 01 '12 at 02:37
1

@mrjimoy_05 [is this what you want? >click here<](http://sqlfiddle.com/#!2/1de17/21) – John Woo Oct 01 '12 at 02:53
1

Exactly. Thanks for your kindness answering my extending question. :) – mrjimoy_05 Oct 01 '12 at 02:56

MySQL GroupBy and Shows it horizontally

1 Answers1

SQLFiddle Demo

Linked