T-SQL - Performing a Join to Retrieve Counts of 0

Question

I am working with a climate database that has three tables. An event table which holds the datetime that a weather event took place, the type of event and the state the event took place in. The second table is a fatality event table which is linked through an ID number from the first table. Finally, there is a table which holds a list of years (1950, 1951, 1952 etc.).

I've written the following:-

SELECT
    State,
    YEAR(BeginDate) as Year,
    COUNT(*) as CountOfEvents
FROM 
    [StormEvents].[dbo].[DT_Event]
JOIN 
    [StormEvents].[dbo].[DT_FatalityEvent] ON [StormEvents].[dbo].[DT_Event].EventID = [StormEvents].[dbo].[DT_FatalityEvent].EventID
WHERE 
    EventType like'%tornado%'
    AND YEAR(BeginDate) >= 1995 
    AND YEAR(BeginDate) <= 2014
GROUP BY 
    State, YEAR(BeginDate)
ORDER BY 
    State, YEAR(BeginDate)

which summarises the number of fatality events per month per state. The query above works but it omits any counts which return a count of 0.

I've attempted adding this line after reading about joins:-

RIGHT JOIN 
    [StormEvents].[dbo].[DT_YearValues] ON YEAR([StormEvents].[dbo].[DT_Event].BeginDate) = [StormEvents].[dbo].[DT_YearValues].[Year]

but this isn't returning 0's in the fields where the count of fatality events for that state and year is 0.

Can anyone suggest where I am going wrong?

Thanks!

What is the requirement for the DT_YearValues? It looks like you will be getting the year just fine using the `YEAR(x)` T-SQL function. — bigtlb, Nov 15 '14 at 18:16
DT_YearValues was put in as a test to see if it would bring back all years for each state, even if the number of events for that state and year was 0. — Mike Upjohn, Nov 15 '14 at 18:48

paparazzo · Answer 1 · 2014-11-15T18:34:03.500

Assuming EventType is in [StormEvents].[dbo].[DT_FatalityEvent]
the following would break a right join
where EventType like'%tornado%'

SELECT State,
       YEAR(BeginDate) as Year,
       COUNT([StormEvents].[dbo].[DT_FatalityEvent].[EventID]) as CountOfEvents
  FROM [StormEvents].[dbo].[DT_Event]
 right JOIN [StormEvents].[dbo].[DT_FatalityEvent] 
    ON [StormEvents].[dbo].[DT_Event].[EventID] = [StormEvents].[dbo].[DT_FatalityEvent].[EventID]
   and EventType like'%tornado%'
   AND YEAR(BeginDate) >= 1995 
   AND YEAR(BeginDate) <= 2014
 GROUP BY State, YEAR(BeginDate)
 ORDER BY State, YEAR(BeginDate)

You need to identify which table the columns are from

Hi there, Thanks for this. I understand what you mean about identifying columns to tables. EventType is part of the DT_Event table. — Mike Upjohn, Nov 15 '14 at 18:44

score 1 · Answer 2 · edited May 23 '17 at 12:12

I think you need a left join. If your DT_FatalityEvent table only has records for fatality events you are performing an inner join, and it should probably be a left join as explained here

Here is a sample of how I would code the query. (NOTE: I usually alias the tables so that it is easier to know which ones you are referring to in the SELECT clause).

I also am assuming you have a NumberOfFatalities for each event, in which case you might want to consider a SUM instead.

SELECT 
  A.State,
  YEAR(A.BeginDate) [Year],
  SUM(ISNULL(B.NumberOfFatalities,0)) [Fatalities]
FROM
  DT_Event A 
LEFT JOIN
  DT_FatalityEvent B ON A.EventId = B.EventID
WHERE 
  A.EventType like'%tornado%'
  AND YEAR(A.BeginDate) >= 1995 AND YEAR(A.BeginDate) <= 2014
GROUP BY 
  A.State, YEAR(A.BeginDate)
ORDER BY 
  A.State, YEAR(A.BeginDate)

This will ensure you get records from the DT_Event table even if there are no DT_FatalityEvent records with matching EventID's. The NumberOfFatalities field (my guess) would be NULL in those cases, so you should probably ensure a value of 0 before using the SUM.

score 0 · Answer 3 · answered Nov 15 '14 at 19:14

Many thanks for the fast reply. I will definitely take on board what was said by Blam about using aliases as this make for much easier code, so thanks for the advice. I was making it too complex, and indeed as bigtlb said there was no need for a YearValues table.

This was the code that did what I needed!

SELECT
    A.State,
    YEAR(A.BeginDate),
    COUNT(B.EventID)
FROM [StormEvents].[dbo].[DT_Event] A
LEFT JOIN [StormEvents].[dbo].[DT_FatalityEvent] B
    ON
        A.EventID = B.EventID
        AND A.EventType like '%tornado%'
GROUP BY State, YEAR(A.BeginDate)
ORDER BY State, YEAR(A.BeginDate)

Thanks for everyone' suggestions! Mike.

T-SQL - Performing a Join to Retrieve Counts of 0

3 Answers3