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 <s...@broadviewsoftware.com>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] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
