RE: [firebird-support] How do you 0/null results to a GROUP BY query

[Leyne, Sean]

> I have query which extract the sales per day from a table
> 
> Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS
> DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO.
> 
> It works 100%, but only returns the dates with sales, I need it to also 
> include
> the dates with zero sales, for example:
> 
> YEARNO DAYNO SUM
> 2012   01    5000
> 2012   02    6000
> 2012   03    0 (or null will be fine)
> 2012   04    7000

Use COALESCE(), as in:

Select 
  EXTRACT(YEAR from DT) as YEARNO, 
  EXTRACT(YEARDAY from DT) AS DAYNO, 
  SUM(COALESCE( DUE, 0)) 
from CLIENT_INVOICES
group by YEARNO, WEEKNO.


Sean