>>BDE SQL Group By Clause Bug ?<< 
Author Message
 >>BDE SQL Group By Clause Bug ?<<

Hi,

I'm not a SQL expert, so this may not be a bug, I hope some kind soul who knows
will let me know.

I did a query using aggregate functions a group by, e.g.
        Select A, B, C, Sum(D) as E
        from T Group by A,B

The BDE keeps giving me the error 'Fields in group by must be in result set!'.
As you can see A and B are already in the result set. The error only go away
when I put C into the group by too. This means the the error message should read
'Fields in result set must be in group by!'. Of all my references, I only found
this info regarding Group By clause in the book 'Delphi : A Developer's Guide'
saying :
        Warning : Whenever an aggregate function is used in a select statement, at
least one non-aggregate field included in the query result must also be included
in the Group By clause.

Here it says at least one, but it appears that all non-aggregate fields have to
be in the Group By clause.

Can someone please tell me is this the way SQL clause Group By works or is this
a bug in BDE please ??

Thanks in advance,
Edison
********  ********
 _______  _______
|   _   ||   _   |
|  0 |  ||  0 |  |
|_______||_______|
    Edison Too
     ________



Mon, 22 Feb 1999 03:00:00 GMT
 >>BDE SQL Group By Clause Bug ?<<

Hi Edision:

We have noticed the same problem and were working on it.  The group by
clause does not seem to work properly.  The following does not work
either.

Select * from A
group by B

Mark



Mon, 22 Feb 1999 03:00:00 GMT
 >>BDE SQL Group By Clause Bug ?<<

Quote:

>Hi,
>I'm not a SQL expert, so this may not be a bug, I hope some kind soul who knows
>will let me know.
>I did a query using aggregate functions a group by, e.g.
>    Select A, B, C, Sum(D) as E
>    from T Group by A,B
>The BDE keeps giving me the error 'Fields in group by must be in result set!'.
> {etc, etc}
>Edison
>********  ********

The way I understand it, the group by clause gives sub-totals, which
are determined when any of the values in the cols specified in the
group by clause change.

Only one row,  the 'sub total' line, is returned, so SQL need to know
what to do with all the other cols in the select.

In the example, A and B are in the group by (one row is returned for
each unique occurrance of A & B), D is the result of a function (only
one value is returned per sub-total).
  C will have many values at the time of the sub-total line, which one
should it choose to return?

Either place C in the group by clause, or use a function in the select
clause (min, max, avg, count, etc.)

It is not ideal, but it works!!

Barbara



Tue, 23 Feb 1999 03:00:00 GMT
 >>BDE SQL Group By Clause Bug ?<<

I know that in MS-Access, when using aggregate functions, you had to include
all the fields that were not in the aggregate functions. According to my ANSI
SQL book, all the column fields used in the SELECT statement must be included
in the Group By clause if not used in an aggregate function. That's standard
SQL. Not a bug in the BDE :) BTW, normally, you could use sub-queries to get
tag other fields onto your summed values, etc... but if your using Local SQL
and D1, you out of luck.


Quote:

>Hi,

>I'm not a SQL expert, so this may not be a bug, I hope some kind soul who knows
>will let me know.

>I did a query using aggregate functions a group by, e.g.
>        Select A, B, C, Sum(D) as E
>        from T Group by A,B

>The BDE keeps giving me the error 'Fields in group by must be in result set!'.
>As you can see A and B are already in the result set. The error only go away
>when I put C into the group by too. This means the the error message should
> read
>'Fields in result set must be in group by!'. Of all my references, I only found
>this info regarding Group By clause in the book 'Delphi : A Developer's Guide'
>saying :
>        Warning : Whenever an aggregate function is used in a select statement,
> at
>least one non-aggregate field included in the query result must also be
> included
>in the Group By clause.
>Here it says at least one, but it appears that all non-aggregate fields have to
>be in the Group By clause.
>Can someone please tell me is this the way SQL clause Group By works or is this
>a bug in BDE please ??

*************************************************************************
* Peter G. Millard      ******************* Maintainer of the VB        *

* http://www.buffnet.net/~millard  ******** Error Reading Drive A.....      *
* http://www.vantek-corp.com             **     Formatting C: Instead    *
*************************************************************************


Sat, 27 Feb 1999 03:00:00 GMT
 >>BDE SQL Group By Clause Bug ?<<

Quote:
>I know that in MS-Access, when using aggregate functions, you had to include
>all the fields that were not in the aggregate functions. According to my ANSI
>SQL book, all the column fields used in the SELECT statement must be included
>in the Group By clause if not used in an aggregate function. That's standard
>SQL. Not a bug in the BDE :) BTW, normally, you could use sub-queries to get
>tag other fields onto your summed values, etc... but if your using Local SQL
>and D1, you out of luck.

        In most cases, you can use the FIRST function to get the value you want.

_
NOTE: This software is currently in early alpha. If you notice any

 \------------------------------------------------------------\


    \     Physically in Church Hill, TN - Logically Not Sure     \
     \------------------------------------------------------------\

Quote:
>>SQUID - The ultimate 95/NT offline databasing reader

**Special Compile: 3.000A (Alpha)


Sun, 28 Feb 1999 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. <<<RDBMS/UNIX/SYBASE ENGINEERS WANTED>>>>Oakland, CA

2. <<<RDBMS/UNIX/SYBASE ENGINEERS WANTED>>>>Oakland, CA

3. <<<RDBMS/UNIX/SYBASE ENGINEERS WANTED>>>>Oakland, CA

4. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology

5. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology

6. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology

7. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology

8. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology

9. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology

10. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology

11. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology

12. US-TX>>>PL/SQL Designers, HOUSTON<<<Yoh Information Technology


 
Powered by phpBB® Forum Software