pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAgregate Problem?
3+ messages / 3 participants
[nested] [flat]
* Agregate Problem?
@ 1999-08-15 14:01 Len Morgan <len-morgan@crcom.net>
1999-08-15 14:27 ` Re: [SQL] Agregate Problem? Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 3+ messages in thread
From: Len Morgan @ 1999-08-15 14:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Agregate Problem?
1999-08-15 14:01 Agregate Problem? Len Morgan <len-morgan@crcom.net>
@ 1999-08-15 14:27 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-15 15:48 ` Re: [SQL] Agregate Problem? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Herouth Maoz @ 1999-08-15 14:27 UTC (permalink / raw)
To: Len Morgan <len-morgan@crcom.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Agregate Problem?
1999-08-15 14:01 Agregate Problem? Len Morgan <len-morgan@crcom.net>
1999-08-15 14:27 ` Re: [SQL] Agregate Problem? Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-08-15 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 1999-08-15 15:48 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: Len Morgan <len-morgan@crcom.net>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-08-15 15:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-15 14:01 Agregate Problem? Len Morgan <len-morgan@crcom.net>
1999-08-15 14:27 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-15 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox