pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect 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>
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 15:26 kaiq@realtyideas.com
parent: Stephan Bauer <stephan_bauer@gmx.de>
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 15:55 neko@kredit.sth.szif.hu
parent: kaiq@realtyideas.com
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 17:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: kaiq@realtyideas.com
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-20 09:58 Vladimir Terziev <vlady@school.digsys.bg>
parent: Stephan Bauer <stephan_bauer@gmx.de>
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