SQL Number of Members in each country whose preference game is set to NES

EXPECTED OUTPUT

COUNT(AUD)  COUNT(EUR)  COUNT(IND)  COUNT(LAO)  COUNT(USA)  COUNT(ZWE)  
    3           2           0           0       2           1

Here is the SQL fiddle

http://sqlfiddle.com/#!9/a15ec9f/3

CodePudding user response：

We pivot.

select *
from   t
pivot(count(MEMBER_ID) for COUNTRY_ID in('AU' as "count(AU)", 'US' as "count(US)", 'LAO' as "count(LAO)", 'IND' as "count(IND)", 'DE' as "count(DE)", 'ZW' as "count(ZW)")) p  

Fiddle

CodePudding user response：

SELECT Member.MemberID, COUNTRY, GAME_TYPE 
FROM MEMBER_TABLE 
INNER JOIN COUNTRY_TABLE ON Member.COUNTRY_ID= COUNTRY_TABLE.COUNTRY_ID 
WHERE Member.GAME_TYPE= 'NES';