Re: [firebird-support] How do you 0/null results to a GROUP BY query
>>> 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 > >> I would crate a reference table that contains all the years and days > >> 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 used to do this way, but with CTE you create that "table" > dynamically, > > another option is using EXECUTE STATEMENT or a SP to loop trough the > days > I saw that. However, I don't see the advantage. Using a reference table > is simpler and clearer than using CTE or other code to generate the > reference values. > Thanks! I will most likely go with the reference table(s) option, but I will test both. Regards Cornie [Non-text portions of this message have been removed]
Re: [firebird-support] How do you 0/null results to a GROUP BY query
Em 24/10/2012 20:37, Doug Chamberlin escreveu: > On 10/24/12 6:25 PM, Alexandre Benson Smith wrote: >> Em 24/10/2012 20:21, Doug Chamberlin escreveu: >>> On 10/24/12 5:54 PM, cornievs 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 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 >>> I would crate a reference table that contains all the years and days >>> 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 used to do this way, but with CTE you create that "table" dynamically, >> another option is using EXECUTE STATEMENT or a SP to loop trough the days > I saw that. However, I don't see the advantage. Using a reference table > is simpler and clearer than using CTE or other code to generate the > reference values. > > > > One don't need to "refill" the values, like at the beginning of each year... of course you could pre-fill i with a century of days... :)
Re: [firebird-support] How do you 0/null results to a GROUP BY query
On 10/24/12 6:25 PM, Alexandre Benson Smith wrote: > Em 24/10/2012 20:21, Doug Chamberlin escreveu: >> On 10/24/12 5:54 PM, cornievs 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 015000 >>> 2012 026000 >>> 2012 030 (or null will be fine) >>> 2012 047000 >> I would crate a reference table that contains all the years and days >> 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 used to do this way, but with CTE you create that "table" dynamically, > another option is using EXECUTE STATEMENT or a SP to loop trough the days I saw that. However, I don't see the advantage. Using a reference table is simpler and clearer than using CTE or other code to generate the reference values.
Re: [firebird-support] How do you 0/null results to a GROUP BY query
Em 24/10/2012 20:21, Doug Chamberlin escreveu: > On 10/24/12 5:54 PM, cornievs 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 015000 >> 2012 026000 >> 2012 030 (or null will be fine) >> 2012 047000 > I would crate a reference table that contains all the years and days > 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 used to do this way, but with CTE you create that "table" dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days
Re: [firebird-support] How do you 0/null results to a GROUP BY query
On 10/24/12 5:54 PM, cornievs 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 015000 > 2012 026000 > 2012 030 (or null will be fine) > 2012 047000 I would crate a reference table that contains all the years and days 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.
Re: [firebird-support] How do you 0/null results to a GROUP BY query
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 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/
Re: [firebird-support] How do you 0/null results to a GROUP BY query
try something like this:
with recursive
Datas (Data) as
(select
Cast('2009-01-01' as Date)
From
rdb$database
union all
Select
Data + 1
From
Datas
Where
Data + 1 <= Cast('2009-01-31' as Date))
select
D.Data, sum(PVI.QuantidadeOriginal)
from
Datas D left join
PedidoVendaItem PVI on (D.Data = PVI.DataEntrega)
group by
D.Data
change the field/table names to fit you needs
HTH
Em 24/10/2012 19:54, cornievs escreveu:
> Hi All
>
> 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 015000
> 2012 026000
> 2012 030 (or null will be fine)
> 2012 047000
>
> etc.
>
> Any help will be appreciated!
>
> Cornie van Schoor
> InfoStar Software
>
>
>
>
>
> ++
>
> 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
>
>
>
>
RE: [firebird-support] How do you 0/null results to a GROUP BY query
> 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 015000 > 2012 026000 > 2012 030 (or null will be fine) > 2012 047000 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
