Replace sum(topay) with coalesce(sum(topay),0).

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of smantti73
Sent: Wednesday, October 05, 2005 11:10 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How to return rows when no data exists?

Hello to each and all.


I'm using Firebird 1.5 with Delphi 2005, and I have a little app that
deals with invoices. Not surprisingly, it contains a "monthly sales"
report that displays sums of invoices per month, on a given time period.
All is well, except I can't figure out how to display months in the
result set that contain no data.

So, for example if I want to return this year's sales per month, I'd
want the result set to look like this:

month amount
1/2005 0
2/2005 2000
3/2005 0
4/2005 5000
5/2005 0

... and so on. I get the data with a query like this:

select
(extract (month from invoicedate) || ''/'' || extract (year from
invoicedate)) as month, sum(topay) as amount from invoice where
invoicedate between :startdate and :enddate group by
extract (year from invoicedate), extract (month from invoicedate)
order by extract (year from invoicedate), extract (month from
invoicedate)

Works like a champ, except that if there are no invoices for a given
month, it will obviously not return a row, so the result set looks like
this:

month amount
2/2005 2000
4/2005 5000

... which is looks daft in a report and doesn't even answer the user's
question of "this years sales" accurately. I've been given a hint that
this could probably be done with a stored procedure, iterating through
the months and returning zeroes for months where no data exists. Trouble
is, I don't know how to do that, so any hints/pointers would be greatly
appreciated.


TIA,
Antti Kurenniemi









------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet
Life.
http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net 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