| Subject | Re: [firebird-support] How do you 0/null results to a GROUP BY query |
|---|---|
| Author | Doug Chamberlin |
| Post date | 2012-10-24T22:21:04Z |
On 10/24/12 5:54 PM, cornievs wrote:
that you want to report on. Then join the data you have to that table
using an outer join so that all dates in the reference table are in the
result set and those that have data from the client invoices will show it.
> I have query which extract the sales per day from a tableI would crate a reference table that contains all the years and days
>
> 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
that you want to report on. Then join the data you have to that table
using an outer join so that all dates in the reference table are in the
result set and those that have data from the client invoices will show it.