>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!!!!!!!
Sir, every dialect of SQL has it's non-ANSI quirks. I've never heard
of Standard-SQL, but ANSI-SQL is a standard established by a
standards organization and used by vendors as a basis for their own
dialects. There has never been a commercial dialect of SQL that fully
matched its contemporary ANSI standard. They have all deviated from
it in some way. In this case, MySQL extends the standard by allowing
you to include an extra column in the SELECT 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) ...
>!!!!!!!!!
You didn't group by month. You grouped by name. Since you didn't use
an aggregate function on fldMONAT, MySQL returns the value found in
that field in the first row in the group. Since you did use an
aggregate function on fldSALARY, MySQL returns the aggregate value
for that group.
>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?????????????????
There are parts of the Oracle SQL dialect that will raise errors in
other RDBMSs, but not in Oracle. There are parts of Transact-SQL that
will raise errors in other RDBMSs, but not in SQL Server. There are
parts of the MySQL SQL dialect that will raise errors in other
RDBMSs, but not in 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 ... !
And don't forget
http://www.mysql.com/doc/E/x/Extensions_to_ANSI.html
> > -----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
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
