I think that is the wrong answer ... Standard-SQL and ANSI-SQL require that
a column that is displayed
and has no grouping function applied to it MUST also appear in the GROUP
BY-clause!!!!!!!
The result:
fldNAME fldMONAT sum(fldSALARY)
boris 1 10200
stephan 1 6000
itself makes no sense ... none earned as much money in January (1) ...
!!!!!!!!!
Postgres gives an error, Oracle gives an error, SQL-Server 7 gives an error
even M$-Access 2000 gives
an error why doesn't MySQL?????????????????
Literature:
http://www.newi.ac.uk/walshea/db1/sql04/index.htm
there: http://www.newi.ac.uk/walshea/db1/sql04/sld015.htm
http://www.nku.edu/~raghavan/gby.html
http://info-it.umsystem.edu/oracle/svslr/svslr.2.1089.html
http://www.sk.postgresql.org/docs/user/sql-select.htm
and so on ... I think ANSI-Specs should even tell you the same ... !
> -----Ursprungliche Nachricht-----
> Von: Bob Hall [mailto:[EMAIL PROTECTED]]
> Gesendet: Freitag, 26. Januar 2001 12:18
> An: [EMAIL PROTECTED]
> Betreff: Re: Strange behavior of MySQL 3.23.xx with GROUP BY ...
>
>
> >I found a very strange behaviour of MySQL 3.23.xx with GROUP BY
> clause ...
> >
> >I have a Table:
> >
> >CREATE TABLE groupbyTEST (
> > fldNAME varchar(20) NOT NULL,
> > fldMONTH tinyint(4) DEFAULT '0' NOT NULL,
> > fldSALARY decimal(10,0) DEFAULT '0' NOT NULL,
> > fldDATE datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
> > PRIMARY KEY (fldNAME, fldMONTH)
> >);
> >
> >with following records:
> >
> >INSERT INTO groupbyTEST VALUES ( 'stephan', '1', '1000', '2001-01-25
> >23:12:29');
> >INSERT INTO groupbyTEST VALUES ( 'boris', '1', '2000', '2001-01-25
> >23:12:45');
> >INSERT INTO groupbyTEST VALUES ( 'stephan', '2', '1200', '2001-01-25
> >23:12:56');
> >INSERT INTO groupbyTEST VALUES ( 'stephan', '3', '1000', '2001-01-25
> >23:13:08');
> >INSERT INTO groupbyTEST VALUES ( 'stephan', '4', '900', '2001-01-25
> >23:14:50');
> >INSERT INTO groupbyTEST VALUES ( 'boris', '2', '1200', '2001-01-25
> >23:15:01');
> >INSERT INTO groupbyTEST VALUES ( 'stephan', '5', '1900', '2001-01-25
> >23:15:16');
> >INSERT INTO groupbyTEST VALUES ( 'boris', '3', '2400', '2001-01-25
> >23:15:39');
> >INSERT INTO groupbyTEST VALUES ( 'boris', '4', '2100', '2001-01-25
> >23:15:50');
> >INSERT INTO groupbyTEST VALUES ( 'boris', '5', '2300', '2001-01-25
> >23:16:01');
> >INSERT INTO groupbyTEST VALUES ( 'boris', '6', '200', '2001-01-25
> >23:16:11');
> >
> >
> >A statement like:
> >
> >SELECT fldNAME, fldMONTH, sum(fldSALARY)
> >FROM groupbyTEST
> >GROUP BY fldNAME
> >HAVING sum(fldSALARY)>200
> >
> >should cause an error because of the missing fldMONTH in the GROUP BY
> >clause,
> >but it is executed without an error and results:
> >
> >fldNAME fldMONAT sum(fldGEHALT)
> >boris 1 10200
> >stephan 1 6000
> >
> >... makes not very much sense ... does it??
>
> Sir, why do you think it should cause an error? The statement is
> valid and the result is correct.
>
> Bob Hall
>
> Know thyself? Absurd direction!
> Bubbles bear no introspection. -Khushhal Khan Khatak
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
