Select Defaults if row is empty 1

[sarahnice]

Hi all,

Here's the deal. I have one table(ItemList) with the following structure:
registration_number varchar(10) PK
progression_id int PK
item_id int PK
subscription_key char(2)
date_of_incidence datetime
status char(1)

Here is a sample of the data:
BD0005 1 1 AC 1994-02-16 00:00:00.0 1
BD0010 1 2 BD 1994-02-17 00:00:00.0 7
BD0011 2 1 AC 1994-02-20 00:00:00.0 8

The status can be one of four possible values (1, 5, 7, 8). I cannot change the structure of the table, create new tables, temp tables or stored proc. The desired results must be returned by a single query.

I need data returned for each subscription_key, in each year for each status, a count of the number of rows.

Here is the query I am starting out with:
select a.subscription_key, DATEPART(YY, a.date_of_incidence) AS year_of_incidence, count(a.item_id) AS count_of_items, a.status
from ItemList a
where DATEPART(YY, a.date_of_incidence) > 1993
group by a.subscription_key, datepart(YY, a.date_of_incidence), a.status

Here is a sample of the result set this query returns:
AC 1994 1 1
AC 1994 17 8

The problem is that the result set contains no rows for status of 5 or 7. This is correct as there are none in the table matching the select criteria. The output should be like the following:
AC 1994 1 1
AC 1994 0 5
AC 1994 0 7
AC 1994 17 8
The ouput should include the empty rows but set the count to 0. I'm not sure how to do this. I tried using joins but I don't know them well enough to get this to work.

I hope this mkes sense and that someone can help me.

Thanks

 

[cmv131]

hmm, not 100% sure but making your count like tis may work


isnull(count(a.item_id),0) AS count_of_items

 

[JamesLean]

If you really need a single query, try this:

SELECT i.subscriptionkey,
  YEAR(i.date_of_incidence),
  COUNT(i.status),
  dt.status
FROM (
    SELECT 1 AS status
    UNION SELECT 5
    UNION SELECT 7
    UNION SELECT 8
  ) dt
  LEFT JOIN itemlist i ON dt.status = i.status
GROUP BY i.subscriptionkey,
  YEAR(i.date_of_incidence),
  dt.status

--James

 

[sarahnice]

Thanks for the help. Your solution works great James