How do I JOIN aggregations results from several SQL SELECTS?

Question

I have a MEMBERS table with the following relevant columns:

 Name  
 JoinDate  
 Level   --1=Gold,2=Silver,3=Bronze**

I want to create a single query to return a membership summary that lists the total number who joined by year and by membership level. Basically, the columns in my resultset would be something like this:

| YEAR | GOLD | SILVER | BRONZE | TOTAL |

I could get the different counts per year for Gold, Silver and Bronze members respectively using the following 3 queries:

SELECT YEAR(JoinDate) AS YEAR, COUNT(*) AS GOLD FROM Members  
WHERE Level=1 GROUP BY YEAR(JoinDate) ORDER BY YEAR(JoinDate)  

SELECT YEAR(JoinDate) AS YEAR, COUNT(*) AS SILVER FROM Members  
WHERE Level=2 GROUP BY YEAR(JoinDate) ORDER BY YEAR(JoinDate)  

SELECT YEAR(JoinDate) AS YEAR, COUNT(*) AS BRONZE FROM Members  
WHERE Level=3 GROUP BY YEAR(JoinDate) ORDER BY YEAR(JoinDate)

I could also get the totals using a similar query:

SELECT YEAR(JoinDate) AS YEAR, COUNT(*) AS TOTAL FROM Members  
GROUP BY YEAR(JoinDate) ORDER BY YEAR(JoinDate)

My problem is I haven't found a way to simplify all these into a single query. How is this done?

Answer 1

Simplest way would be:

SELECT YEAR(JoinDate) AS YEAR,
    SUM(case when Level = 1 then 1 else 0 end) AS GoldCount,
    SUM(case when Level = 2 then 1 else 0 end) AS SilverCount,
    SUM(case when Level = 3 then 1 else 0 end) AS BronzeCount
FROM Members  
GROUP BY YEAR(JoinDate) ORDER BY YEAR(JoinDate)

Answer 2

You're looking for what's called a cross-tab query or pivot table.

This should do it for you..

SELECT      YEAR(JoinDate) YEAR,  
            SUM(CASE [Level] WHEN 1 THEN 
                    1 ELSE 0 END) Gold, 
            SUM(CASE [Level] WHEN 2 THEN 
                    1 ELSE 0 END) Silver, 
            SUM(CASE [Level] WHEN 3 THEN 
                    1 ELSE 0 END) Bronze,
        COUNT([Level]) Total
FROM        members
GROUP BY    YEAR(JoinDate) 
ORDER BY    YEAR(JoinDate)

More on cross-tab queries here.

Answer 3

And to add the total to Juliet's answer, just add COunt(*)

SELECT YEAR(JoinDate) AS YEAR,    
     SUM(case when Level = 1 then 1 else 0 end) AS GoldCount,    
     SUM(case when Level = 2 then 1 else 0 end) AS SilverCount,    
     SUM(case when Level = 3 then 1 else 0 end) AS BronzeCount,
     Count(*) TotalCount
FROM Members  
GROUP BY YEAR(JoinDate) 
ORDER BY YEAR(JoinDate)