pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Agregate Problem?
3+ messages / 3 participants
[nested] [flat]

* Agregate Problem?
@ 1999-08-15 14:01  Len Morgan <len-morgan@crcom.net>
  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:27  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Len Morgan <len-morgan@crcom.net>
  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 15:48  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Herouth Maoz <herouth@oumail.openu.ac.il>
  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