GROUP BY in Delphi's Local SQL 
Author Message
 GROUP BY in Delphi's Local SQL

I'm trying to execute the following simple query: (on delphi 3.01 C/S,
BDE 4.01)
-------------------------------
SELECT name,country
FROM customers
WHERE age > 30
GROUP BY country
-------------------------------

And I get the error text: "When GROUP BY exists, every simple field in
the projectors must be in GROUP BY.."

Am I Dreaming this??? Local SQL does not allow me to extract the name of
my 30+ customers grouped by country !!!

Any idea how to fix this ?

Thanks,
Amit



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

Quote:

> I'm trying to execute the following simple query: (on Delphi 3.01 C/S,
> BDE 4.01)
> -------------------------------
> SELECT name,country
> FROM customers
> WHERE age > 30
> GROUP BY country
> -------------------------------

> And I get the error text: "When GROUP BY exists, every simple field in
> the projectors must be in GROUP BY.."

> Am I Dreaming this??? Local SQL does not allow me to extract the name of
> my 30+ customers grouped by country !!!

> Any idea how to fix this ?

> Thanks,
> Amit

You should have written this, as the message told you :
SELECT name,country
FROM customers
WHERE age > 30
GROUP BY name, country
;-)


Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

Quote:

>I'm trying to execute the following simple query: (on Delphi 3.01 C/S,
>BDE 4.01)
>-------------------------------
>SELECT name,country
>FROM customers
>WHERE age > 30
>GROUP BY country
>-------------------------------

>And I get the error text: "When GROUP BY exists, every simple field in
>the projectors must be in GROUP BY.."

>Am I Dreaming this??? Local SQL does not allow me to extract the name of
>my 30+ customers grouped by country !!!

>Any idea how to fix this ?

>Thanks,
>Amit

You probably want ORDER BY instead of GROUP BY.  GROUP BY will roll up
your result set so you only have one entry per grouped field.  And in
order for that to be useful you must include an aggregate function
like COUNT() or SUM().  So if, say, you wanted to find out how MANY
31+ year old customers you have in each country, you would write:

SELECT country, COUNT(country)
  FROM customers
  WHERE age > 30
  GROUP BY country

Replacing your original GROUP BY with ORDER BY will give you a list of
your customer names, sorted by each country.  And yes, one could
agruably call that "grouped by", but that word has a very distinct
meaning in SQL.  Hope that helps.

-Marcus



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

'age' needs to be in part of you selected fields.



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

Quote:

>I'm trying to execute the following simple query: (on Delphi 3.01 C/S,
>BDE 4.01)
>-------------------------------
>SELECT name,country
>FROM customers
>WHERE age > 30
>GROUP BY country
>-------------------------------

>And I get the error text: "When GROUP BY exists, every simple field in
>the projectors must be in GROUP BY.."

>Am I Dreaming this??? Local SQL does not allow me to extract the name of
>my 30+ customers grouped by country !!!

>Any idea how to fix this ?

This is not a local SQL limitation. This is a standard SQL rule (per the
industry-approved SQL-92 specification). For a column to be included in the
SELECT clause columns list of a query that includes a GROUP BY clause,
every column _must_ meet one of two criteria:

1. If a column is not aggregated (i.e., a "simple field"), it must also
   appear in the GROUP BY columns list.

2. If a column is not in the GROUP BY columns list, it must be the subject
   of an aggregate function.

Your column NAME does not meet either criteria, making the SQL statement
invalid.

Normally, a GROUP BY clause is used with aggregate functions (SUM, AVG,
MAX, etc.) to perform cross-row calculations. The addition of a GROUP BY
clause causes the calculation to be performed once for each group with the
same value in one or more columns specified in the GROUP BY columns list.
You can attain the result you intended with a qualified statement like the
one below:

  SELECT name, country
  FROM customers
  WHERE (age > 30)
  GROUP BY country, name

I suspect, though, that it is not the GROUP BY clause you really wanted.
The ORDER BY clause will cause all rows with the same value in the COUNTRY
column to appear together (contiguous in the result set), with all groups
of COUNTRY values sorted alphabetically. Within each group of COUNTRY
values, the NAME values will appear, sorted alphabetically.

  SELECT name,country
  FROM customers
  WHERE (age > 30)
  ORDER BY country, name

**************************************************************************
Steve Koterski
Borland International, Inc.
http://www.borland.com/delphi
(Remove the "SPICEDHAM2" from the address. Death to spam-bots!)



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

Quote:

>I'm trying to execute the following simple query: (on Delphi 3.01 C/S,
>BDE 4.01)
>-------------------------------
>SELECT name,country
>FROM customers
>WHERE age > 30
>GROUP BY country
>-------------------------------

>And I get the error text: "When GROUP BY exists, every simple field in
>the projectors must be in GROUP BY.."

>Am I Dreaming this??? Local SQL does not allow me to extract the name of
>my 30+ customers grouped by country !!!

You should use ORDER BY instead of GROUP BY.  GROUP BY is for
something different, the result set will include one single record for
each GROUP BY set.

-------------------------------------
Larry Lustig
http://www.pipeline.com/~nyguide/
NY-DC 2 Day Tours, NY-PHILADELPHIA 1 Day Tour,
NY-Shopping Outlet 1 Day Tour.

Larry's Opinionated Guide to New York City
(for Cheapskates): The information you need
from a real New Yorker.  Available at Travel
Bookstores, or from the author
(email for information).



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

Hi:
If you use 'group by',You must use the sum function
----------------------
SELECT name,country,count(*),sum(xx),avg(xx)..........
FROM customers
WHERE age > 30
GROUP BY country
----------------------
Robert


Quote:
>I'm trying to execute the following simple query: (on Delphi 3.01 C/S,
>BDE 4.01)
>-------------------------------
>SELECT name,country
>FROM customers
>WHERE age > 30
>GROUP BY country
>-------------------------------

>And I get the error text: "When GROUP BY exists, every simple field in
>the projectors must be in GROUP BY.."

>Am I Dreaming this??? Local SQL does not allow me to extract the name of
>my 30+ customers grouped by country !!!

>Any idea how to fix this ?

>Thanks,
>Amit



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

For getting all countries and names from the customer's table its quite
enough to use
  SELECT distinct country,name
  FROM customers
  WHERE age > 30
Only for calculating or counting you will need group by:
  SELECT Country,Count(*) CustomCount
  FROM customers
  WHERE age > 30
  Group by Country
will give you the the count of customers  (in the field named
"CustomCount") in every country 30 years or older.

mfg Dietmar Brckmann



Quote:
> I'm trying to execute the following simple query: (on Delphi 3.01 C/S,
> BDE 4.01)
> -------------------------------
> SELECT name,country
> FROM customers
> WHERE age > 30
> GROUP BY country
> -------------------------------

> And I get the error text: "When GROUP BY exists, every simple field in
> the projectors must be in GROUP BY.."

> Am I Dreaming this??? Local SQL does not allow me to extract the name of
> my 30+ customers grouped by country !!!

> Any idea how to fix this ?

> Thanks,
> Amit



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

It looks to me like your SQL is wrong.

Use a GROUP BY when you are going to aggregate some data fromthe underlying
table structure.  When you do this then EVERY item in the select list which
is NOT aggreagted must be in the group by clause.

i.e. use it to find the customer with the 'greatest' name  in each country
i.e
select MAX(name), country
from custoemrs
where ...
group by country

Hope this helps

Alan


Quote:
> I'm trying to execute the following simple query: (on Delphi 3.01 C/S,
> BDE 4.01)
> -------------------------------
> SELECT name,country
> FROM customers
> WHERE age > 30
> GROUP BY country
> -------------------------------

> And I get the error text: "When GROUP BY exists, every simple field in
> the projectors must be in GROUP BY.."

> Am I Dreaming this??? Local SQL does not allow me to extract the name of
> my 30+ customers grouped by country !!!

> Any idea how to fix this ?

> Thanks,
> Amit



Wed, 18 Jun 1902 08:00:00 GMT  
 GROUP BY in Delphi's Local SQL

You need to differentiate between GROUP and ORDER

SELECT name,country
FROM customers
WHERE age > 30
ORDER BY country

will give you a list of the over 30's country by country, the GROUP BY
can be used as Alan said to get stats so

SELECT Count(name), country
FROM customers
WHERE age > 30
GROUP BY country

would give you a count of how many over 30's there are in each country

Quote:

> It looks to me like your SQL is wrong.

> Use a GROUP BY when you are going to aggregate some data fromthe
> underlying
> table structure.  When you do this then EVERY item in the select list
> which
> is NOT aggreagted must be in the group by clause.

> <snip>
> Hope this helps

> Alan



> > I'm trying to execute the following simple query: (on Delphi 3.01
> C/S,
> > BDE 4.01)
> > -------------------------------
> > SELECT name,country
> > FROM customers
> > WHERE age > 30
> > GROUP BY country
> > -------------------------------

> <snip>
> > Thanks,
> > Amit



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. HELP !: Local SQL and Group By

2. Aggregate functions and Group By problems in Local SQL

3. How to GROUP on EXTRACTed field in Local SQL

4. Group by... Having in Local SQL

5. Local SQL group

6. SQL and the GROUP BY clause with local Paradox tables

7. Using GROUP BY in local SQL

8. Oxfordshire, UK - new local delphi user group.

9. Cant't do it in Local SQL!

10. Los Angeles Delphi User's Group

11. Los Angeles Delphi User's Group

12. Delphi 2.0 MS SQL 6.5 Support group

 

 
Powered by phpBB® Forum Software