i need to pivot a table in sqlite-CodePudding

select team,medal,COUNT(*) AS no_of_medals from athletes where medal != 'NA' GROUP BY team,medal order by team,medal

I need output in the following format

team gold silveR bronze
USA  3     4     6

CodePudding user response：

select * from (
select team,medal,COUNT(*) AS no_of_medals from athletes where medal != 'NA' GROUP BY team,medal order by team,medal
)
pivot (sum(no_of_medals) for medal in ('gold', 'silveR', 'bronze'));

CodePudding user response：

Use TOTAL() aggregate function for each type of medal:

SELECT team, 
       TOTAL(medal = 'Gold') gold,
       TOTAL(medal = 'Silver') silver,
       TOTAL(medal = 'Bronze') bronze
FROM athletes 
GROUP BY team
ORDER BY team;