pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
select with group by problem
5+ messages / 5 participants
[nested] [flat]

* select with group by problem
@ 1999-12-17 13:11 Stephan Bauer <stephan_bauer@gmx.de>
  1999-12-17 15:26 ` Re: [SQL] select with group by problem kaiq@realtyideas.com
  1999-12-20 09:58 ` Re: [SQL] select with group by problem Vladimir Terziev <vlady@school.digsys.bg>
  0 siblings, 2 replies; 5+ messages in thread

From: Stephan Bauer @ 1999-12-17 13:11 UTC (permalink / raw)
  To: pgsql-sql; +Cc: Dietmar Kling <dietmar.kling@sam-net.de>

Hello,

could anyone tell me why the second select statement returns one row.

regards

Stephan

xtra=> select * from
warenkorb;                                                
cookie|datum|artnr|artnr1|artnr2|artikelname|farbe|groesse|menge|preis
------+-----+-----+------+------+-----------+-----+-------+-----+-----
(0 rows)

xtra=> SELECT artnr, artnr1, artnr2, artikelname, farbe, groesse, SUM(
menge ),
 preis FROM warenkorb WHERE cookie = 'asdfasdf' GROUP BY artnr, artnr1,
artnr2,
 artikelname, farbe, groesse, preis;
artnr|artnr1|artnr2|artikelname|farbe|groesse|sum|preis
-----+------+------+-----------+-----+-------+---+-----
     |      |      |           |     |       |   |     
(1 row)



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] select with group by problem
  1999-12-17 13:11 select with group by problem Stephan Bauer <stephan_bauer@gmx.de>
@ 1999-12-17 15:26 ` kaiq@realtyideas.com
  1999-12-17 15:55   ` Re: [SQL] select with group by problem neko@kredit.sth.szif.hu
  1999-12-17 17:09   ` Re: [SQL] select with group by problem Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 5+ messages in thread

From: kaiq@realtyideas.com @ 1999-12-17 15:26 UTC (permalink / raw)
  To: Stephan Bauer <stephan_bauer@gmx.de>; +Cc: pgsql-sql@postgreSQL.org, Dietmar Kling <dietmar.kling@sam-net.de>

because there is a sum. sum always results something, even
the something's value is a null ;-)


On Fri, 17 Dec 1999, Stephan Bauer wrote:

> Hello,
> 
> could anyone tell me why the second select statement returns one row.
> 
> regards
> 
> Stephan
> 
> xtra=> select * from
> warenkorb;                                                
> cookie|datum|artnr|artnr1|artnr2|artikelname|farbe|groesse|menge|preis
> ------+-----+-----+------+------+-----------+-----+-------+-----+-----
> (0 rows)
> 
> xtra=> SELECT artnr, artnr1, artnr2, artikelname, farbe, groesse, SUM(
> menge ),
>  preis FROM warenkorb WHERE cookie = 'asdfasdf' GROUP BY artnr, artnr1,
> artnr2,
>  artikelname, farbe, groesse, preis;
> artnr|artnr1|artnr2|artikelname|farbe|groesse|sum|preis
> -----+------+------+-----------+-----+-------+---+-----
>      |      |      |           |     |       |   |     
> (1 row)
> 
> ************
> 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] select with group by problem
  1999-12-17 13:11 select with group by problem Stephan Bauer <stephan_bauer@gmx.de>
  1999-12-17 15:26 ` Re: [SQL] select with group by problem kaiq@realtyideas.com
@ 1999-12-17 15:55   ` neko@kredit.sth.szif.hu
  1 sibling, 0 replies; 5+ messages in thread

From: neko@kredit.sth.szif.hu @ 1999-12-17 15:55 UTC (permalink / raw)
  To: kaiq@realtyideas.com; +Cc: Stephan Bauer <stephan_bauer@gmx.de>; pgsql-sql@postgreSQL.org, Dietmar Kling <dietmar.kling@sam-net.de>

> > xtra=> select * from warenkorb;                                                
> > cookie|datum|artnr|artnr1|artnr2|artikelname|farbe|groesse|menge|preis
> > ------+-----+-----+------+------+-----------+-----+-------+-----+-----
> > (0 rows)
> > 
> > xtra=> SELECT artnr, artnr1, artnr2, artikelname, farbe, groesse, SUM(
> > menge ),
> >  preis FROM warenkorb WHERE cookie = 'asdfasdf' GROUP BY artnr, artnr1,
> > artnr2,
> >  artikelname, farbe, groesse, preis;
> > artnr|artnr1|artnr2|artikelname|farbe|groesse|sum|preis
> > -----+------+------+-----------+-----+-------+---+-----
> >      |      |      |           |     |       |   |     
> > (1 row)
try this:
xtra=> SELECT artnr, artnr1, artnr2, artikelname, farbe, groesse, SUM(menge),
xtra-> preis FROM warenkorb WHERE cookie = 'asdfasdf' GROUP BY artnr, artnr1,
xtra-> artnr2, artikelname, farbe, groesse, preis HAVING count(*)>0;




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] select with group by problem
  1999-12-17 13:11 select with group by problem Stephan Bauer <stephan_bauer@gmx.de>
  1999-12-17 15:26 ` Re: [SQL] select with group by problem kaiq@realtyideas.com
@ 1999-12-17 17:09   ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 1999-12-17 17:09 UTC (permalink / raw)
  To: kaiq@realtyideas.com; +Cc: Stephan Bauer <stephan_bauer@gmx.de>; pgsql-sql@postgreSQL.org, Dietmar Kling <dietmar.kling@sam-net.de>

<kaiq@realtyideas.com> writes:
> because there is a sum. sum always results something, even
> the something's value is a null ;-)

Yeah, but in GROUP BY mode, that SUM() should return one row per group.
If no input, then there are no groups, so there should be no output.
This is in fact a bug in Postgres: it produces a row anyway, which is
right for the un-GROUPed case but not when GROUPing.

The bug is fixed for the next release.  In the meantime you might want
to use a workaround like adding "HAVING COUNT(*) > 0" to the query.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] select with group by problem
  1999-12-17 13:11 select with group by problem Stephan Bauer <stephan_bauer@gmx.de>
@ 1999-12-20 09:58 ` Vladimir Terziev <vlady@school.digsys.bg>
  1 sibling, 0 replies; 5+ messages in thread

From: Vladimir Terziev @ 1999-12-20 09:58 UTC (permalink / raw)
  To: Stephan Bauer <stephan_bauer@gmx.de>; +Cc: pgsql-sql@postgresql.org, Dietmar Kling <dietmar.kling@sam-net.de>


   The problem is in function SUM. That function always returns not null
result as rows.

   Regards!

	Vladimir

On Fri, 17 Dec 1999, Stephan Bauer wrote:

> Hello,
> 
> could anyone tell me why the second select statement returns one row.
> 
> regards
> 
> Stephan
> 
> xtra=> select * from
> warenkorb;                                                
> cookie|datum|artnr|artnr1|artnr2|artikelname|farbe|groesse|menge|preis
> ------+-----+-----+------+------+-----------+-----+-------+-----+-----
> (0 rows)
> 
> xtra=> SELECT artnr, artnr1, artnr2, artikelname, farbe, groesse, SUM(
> menge ),
>  preis FROM warenkorb WHERE cookie = 'asdfasdf' GROUP BY artnr, artnr1,
> artnr2,
>  artikelname, farbe, groesse, preis;
> artnr|artnr1|artnr2|artikelname|farbe|groesse|sum|preis
> -----+------+------+-----------+-----+-------+---+-----
>      |      |      |           |     |       |   |     
> (1 row)
> 
> ************
> 
> 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~1999-12-20 09:58 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-17 13:11 select with group by problem Stephan Bauer <stephan_bauer@gmx.de>
1999-12-17 15:26 ` kaiq@realtyideas.com
1999-12-17 15:55   ` neko@kredit.sth.szif.hu
1999-12-17 17:09   ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-20 09:58 ` Vladimir Terziev <vlady@school.digsys.bg>

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