SQL Server Pivot clause with Count

user_13373464 0
Viewed 98

I'm using Microsoft SQL Server and trying to write the following query below. I want to use the Pivot clause to get the count in gold medals for USA and Russia from the Olympics 2000. But My output is Zero for both countries. I know that I can use the group by to get the desired result (see print screen below). But how can do this with the pivot clause?

Please see print screens of the dataset and the output below

select 
    'Gold' as total_m, 
    ['USA'] as USA, ['RUS'] as RUS
from 
    (select 
         country, medal, year
     from 
         summer
     where 
         medal = 'Gold'
         and year = 2000 
         and country in ('USA', 'RUS')) as SourceTable
pivot
    (count(medal)   
     for country in (['USA'],['RUS'])) as PivotTable;

Dataset

enter image description here

Output

enter image description here

Group by

enter image description here

pivot count tsql sql-server sql
2 Answers

Remove quotes from pivot column list,

select 'Gold' as total_m, [USA] as USA, [RUS] as RUS
from 
    (select country, medal, year
     from summer
     where medal = 'Gold'
       and year = 2000 
       and country in ('USA', 'RUS')) as SourceTable
pivot
    (count(medal)   
     for country in ([USA],[RUS])) as PivotTable;
user_138780230

I find that it is simpler to express this with conditional aggregation. This is a portable syntax, that works across most databases, and that is somehow less convoluted that vendor-specific pivot syntax:

select mdel, 
    sum(case when country = 'USA' then 1 else 0 end) as USA,
    sum(case when country = 'RUS' then 1 else 0 end) as RUS
from summer
where medal = 'Gold' and year = 2000 and country in ('USA', 'RUS')
group by medal
user_106767160
Related