But this won't return rows for dates where there is no record in the source
table.

On Wed, Oct 24, 2012 at 6:02 PM, Leyne, Sean <Sean@...>wrote:

> **
>
>
>
> > 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
>
>
>


[Non-text portions of this message have been removed]