Postgres Pro
Downloads
Home   >   mailing lists

Thread: select with group by problem

select with group by problem

From
Stephan Bauer
Date:
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)

Re: [SQL] select with group by problem

From
Date:
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)
> 
> ************
>

Re: [SQL] select with group by problem

From
neko@kredit.sth.szif.hu
Date:
> > 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;

Re: [SQL] select with group by problem

From
Tom Lane
Date:
<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

Re: [SQL] select with group by problem

From
Vladimir Terziev
Date:
  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)
> 
> ************
> 
>