How to return zero using Count() with multiple tables

Question

i have three tables (SQL Server)

Month - month_id, month name, ....

Award - award_id, award name, ....

Nomination - fk_award_id, fk_month_id, name, address,...

I need to count the number of different types of awards awarded per month while returning 0 in cases where nobody is awarded

for ex.
the results should look like

April-09     Gold         10   
April-09     Silver        2  
April-09     Bronze        0    
May-09       Gold          2   
May-09       Silver        1   
May-09       Bronze        0

Is this possible?

Answer 1

group by month_id, award_id plus out join will do the trick

Answer 2

Something like this is what you need. Untested, but principle is there:

SELECT m.Monthname, a.Awardname, COUNT(*)
    FROM Month m
       LEFT JOIN Nomination n ON n.fK_Month_ID = m.MonthID
       LEFT JOIN Award a ON n.FK_Award_ID = a.AwardID
    GROUP BY m.Monthname, a.Awardname

The key to this is the GROUP BY clause.

Answer 3

Some data to test:

DECLARE @months TABLE
( month_id INT IDENTITY,
month_name VARCHAR(50)
)

INSERT INTO @months(month_name) VALUES ('April-09')
INSERT INTO @months(month_name) VALUES ('May-09')

DECLARE @awards TABLE
( award_id INT IDENTITY,
award_name VARCHAR(50)
)

INSERT INTO @awards(award_name) VALUES ('Bronze')
INSERT INTO @awards(award_name) VALUES ('Silver')
INSERT INTO @awards(award_name) VALUES ('Gold')

DECLARE @nominations TABLE
( fk_month_id INT,
fk_award_id INT,
other_field VARCHAR(10)
)

INSERT INTO @nominations(fk_month_id,fk_award_id,other_field) VALUES (1,1,'1')
INSERT INTO @nominations(fk_month_id,fk_award_id,other_field) VALUES (1,1,'2')
INSERT INTO @nominations(fk_month_id,fk_award_id,other_field) VALUES (2,2,'3')
INSERT INTO @nominations(fk_month_id,fk_award_id,other_field) VALUES (2,1,'4')
INSERT INTO @nominations(fk_month_id,fk_award_id,other_field) VALUES (3,1,'5')
INSERT INTO @nominations(fk_month_id,fk_award_id,other_field) VALUES (3,2,'6')
INSERT INTO @nominations(fk_month_id,fk_award_id,other_field) VALUES (3,2,'7')

And the query

SELECT month_name, award_name, ISNULL(cnt,0) 
FROM @months
CROSS JOIN @awards
LEFT JOIN ( 
    SELECT fk_month_id,fk_award_id, COUNT(*) AS cnt
    FROM @nominations 
    GROUP BY fk_month_id,fk_award_id
) fk ON fk_month_id = month_id AND fk_award_id = award_id

The result is:

April-09  Bronze  2
April-09  Silver  0
April-09  Gold    0
May-09    Bronze  1
May-09    Silver  1
May-09    Gold    0