Обсуждение: select with group by problem

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)

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)
> 
> ************
> 

> > 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;

<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

  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)
> 
> ************
> 
>