How to print column names as count of rows in mysql?

Question

I have following table

opposition    |   dismissals
--------------+-------------
Australia     |   lbw
South Africa  |   run_out
England       |   bowled
Australia     |   run_out
England       |   lbw

I want to print output as follows

Opposition    |  lbw  | run_out  | bowled
--------------+-------+----------+-------
Australia     |  1    | 1        | 0
England       |  1    | 0        | 1
South Africa  |  0    | 1        | 0

I cannot figure out how to use row values as column name and the print corresponding count. Can anyone can suggest in mysql how to do it? I am stuck at this question for days now. Even tried googling but I couldn't find anything.

This is called "pivoting" for when you need to google this one in the future. Lots of answers here: [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — JNevill, Oct 30 '20 at 17:37

score 1 · Accepted Answer · answered Oct 30 '20 at 17:37

1

Use conditional aggregation. In MySQL:

select opposition,
    sum(dismissals = 'lbw'    ) lbw,
    sum(dismissals = 'run_out') run_out,
    sum(dismissals = 'bowled' ) bowled
from mytable
group by opposition

answered Oct 30 '20 at 17:37

GMB

216,147
25
84
135

1

Thank you for your answer... But I had a doubt, shouldn't be there "as" keyword before every column name.... That is sum(dismissals ='lbw' ) AS lbw and so on.... I am not sure just asking... – Oct 31 '20 at 14:29
2

@John: this keyword is optional. – GMB Oct 31 '20 at 15:07
2

oh ok.... I am new so didn't know about it.... Thanks for clarification... – Oct 31 '20 at 15:48

score 0 · Answer 2 · answered Oct 30 '20 at 21:30

Considering the distinctly chosen values of dismissals column could differ, the dynamic pivoting might be a better alternative than the static one, such as

SET @sql = NULL;

SELECT GROUP_CONCAT(
             CONCAT('SUM( dismissals =''', dismissals, ''' ) AS ',dismissals)
       )
  INTO @sql
  FROM ( SELECT DISTINCT dismissals FROM tab ) t;
       
SET @sql = CONCAT('SELECT opposition,',@sql,' FROM tab GROUP BY opposition'); 


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo

How to print column names as count of rows in mysql?

2 Answers2