Thread: Agregate Problem?

I am having a problem with queries that have an aggregate (sum in this case)
when no rows meet my criteria.  I still get back a row with null values for
all fields.  Is this the correct behavior?  I am trying to sum up daily
sales quanties by items and if I don't have any sales on a particular day, I
want to print nothing.  I thought I could do this by detecting a 0 row count
but since I have the aggregates, I still 1 row.

Example:

SELECT itemno,sum(qty) FROM sales WHERE sale_date = 'now()::date'

I would expect to get back zero rows if there were no sales today but I get
back 1.  Of course I also get back 1 row if I only sold 1 item.  Is this a
bug or a "feature?"

Thanks

Len Morgan

At 17:01 +0300 on 15/08/1999, Len Morgan wrote:


>
> SELECT itemno,sum(qty) FROM sales WHERE sale_date = 'now()::date'
>
> I would expect to get back zero rows if there were no sales today but I get
> back 1.  Of course I also get back 1 row if I only sold 1 item.  Is this a
> bug or a "feature?"

This is a correct behaviour when there is no GROUP BY. PostgreSQL does this
in GROUP BY, too, unfortunately. But it's not a big problem - you merely
have to check whether the returned row has a NULL value instead of the sum.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

> At 17:01 +0300 on 15/08/1999, Len Morgan wrote:
>> SELECT itemno,sum(qty) FROM sales WHERE sale_date = 'now()::date'

This is invalid as it stands --- you need "GROUP BY itemno" because of
the aggregate function.  (IIRC, Postgres versions before 6.5 were a bit
lax about checking for that error.)

>> I would expect to get back zero rows if there were no sales today but I get
>> back 1.  Of course I also get back 1 row if I only sold 1 item.  Is this a
>> bug or a "feature?"

Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> This is a correct behaviour when there is no GROUP BY. PostgreSQL does this
> in GROUP BY, too, unfortunately.

I believe we finally agreed that producing an empty row when there is a
GROUP BY is a bug (there was some debate about it).  I intend to change
that behavior for 6.6, but haven't actually done it yet...
        regards, tom lane