How to return all rows on a SUM/COUNT query?

[bitwise]

I have a query that simply counts how many items exist for a particular status within a given data range. The query works...

SELECT DATE(MY_TIMESTAMP) AS DATE,
SUM(CASE WHEN STATUS_ID=10 THEN 1 ELSE 0 END) AS STATUS10,
SUM(CASE WHEN STATUS_ID=20 THEN 1 ELSE 0 END) AS STATUS20
FROM "MYDBB"."TABLE0"
WHERE
DATE(TIMESTAMP) >= DATE('2011-12-11') AND
DATE(TIMESTAMP) <= DATE('2011-12-17')
GROUP BY DATE(MY_TIMESTAMP);

The sorta problem is that it only returns rows for a given date where at least one of the counts exists. Can I get it to return all dates within the range provided with values of 0, 0?

 

[SkipVought]

hi,

Something like...

WHERE
( 
  (
      DATE(TIMESTAMP) >= DATE('2011-12-11')
  AND DATE(TIMESTAMP) <= DATE('2011-12-17')
  )
   OR TIMESTAMP Is Null
)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bitwise]

Hi, thanks for the reply. That won't work because the TIMESTAMP field isn't the NULL item in question. The TIMESTAMP is never NULL. What might not exist within a date range is something with the STATUS_ID of 10 or 20. There are many statuses. So, for those dates without an item with the STATUS_ID of 10 or 20 no row is returned. I would like...

2011-12-11 0 0

However, what is returned is no row at all.

 

[blom0344]

SELECT DATE(MY_TIMESTAMP) AS DATE,
SUM(CASE WHEN STATUS_ID=10 THEN 1 ELSE 0 END) AS STATUS10,
SUM(CASE WHEN STATUS_ID=20 THEN 1 ELSE 0 END) AS STATUS20
FROM "MYDBB"."TABLE0"
WHERE
DATE(TIMESTAMP) >= DATE('2011-12-11') AND
DATE(TIMESTAMP) <= DATE('2011-12-17')
GROUP BY DATE(MY_TIMESTAMP)
UNION ALL
SELECT DATE(MY_TIMESTAMP) AS DATE,
0 AS STATUS10,
0 AS STATUS20
FROM "MYDBB"."TABLE0"
WHERE
DATE(TIMESTAMP) >= DATE('2011-12-11') AND
DATE(TIMESTAMP) <= DATE('2011-12-17') and
STATUS_ID NOT IN (10,20)

Ties Blom