1

How can I show the grouped column into a single row to make it visually clear with the count value? I think there must be a concise way instead of joining multiple table to show a single row.

My table (tb) looks like this

Name      Subjects
David     Geography
David     Geography
Sherry    History
Paul      History
Paul       Math
Ken       Chemistry

I want to show the table like this

Name    Geography    History     Math     Chemistry   Total
David   2            0           0        0           2
Sherry  0            2           0        0           2
Paul    0            1           1        0           2
Ken     0            0           0        1           1

I'm doing like multiple join tables 

SELECT g.name, g.count, h.count, m.count, c.count, 
g.count + h.count + m.count + c.count as Total
FROM
(SELECT name, count(*) as count
FROM tb 
WHERE Subjects = "Geography"
GROUP BY Name) as g
JOIN
(SELECT name, count(*) as count
FROM tb
WHERE Subjects = "History"
GROUP BY Name) as h
ON g.name = h.name
JOIN 
(SELECT name, count(*) as count
FROM tb
WHERE Subjects = "Math"
GROUP BY Name) as m
ON g.name = m.name
JOIN
(SELECT name, count(*) as count
FROM tb
WHERE Subjects = "Chemistry"
GROUP BY Name) as c
ON g.name = c.name
Kenneth
  • 45
  • 2
  • 7
  • Perhaps you didn't even know what to search for, but a search for "pivot table SQL" would have turned up a lot of help. – Tim Biegeleisen Jul 11 '18 at 00:23
  • Great! I'll check "pivot table SQL" tutorial. I know a bit of SQL basics. Not really an expert. Thanks! – Kenneth Jul 11 '18 at 00:35
  • `SELECT Name, COUNT(CASE WHEN Subjects = 'Geography' THEN 1 END) AS Geography ... GROUP BY Name` ... then add case expressions for the other subjects as well. – Tim Biegeleisen Jul 11 '18 at 00:36
  • @TimBiegeleisen pretty neat with CASE WHEN. It's the key word that I don't know "Pivot Table" to begin with. Thanks. – Kenneth Jul 11 '18 at 11:18
  • @a_horse_with_no_name, BigQuery. I updated the tags. – Kenneth Jul 11 '18 at 11:19
  • @Kenneth - does referenced answer applicable to your case? if no - why. I am asking because this was mark as a duplicated for mySQL butt hen you added and clarified that it is for BigQuery – Mikhail Berlyant Jul 11 '18 at 18:47
  • @MikhailBerlyant, CASE WHEN also works in BigQuery either in #standardSQL or #--use_legacy_sql=false as well. – Kenneth Jul 11 '18 at 19:20
  • sure i am aware of this :o) just wanted to double check if your got this. so, looks like you are good here. great! – Mikhail Berlyant Jul 11 '18 at 19:56

0 Answers0