Link to home
Start Free TrialLog in
Avatar of lgc5800
lgc5800Flag for Belgium

asked on

SQL PROBLEM

Hi,

i have a problem with this sql :

SELECT count(ado_persons.lastname), ado_persons.firstname, ado_persons.country, ado_persons.grade
FROM ado_persons
WHERE (ado_persons.grade = 'Architect')

I work with delphi5, ado tables and ado query, connecting to an MDB ACCESS 97 database. The problem is with the reserved word COUNT. When i use it alone that work, but when i insert COUNT, SUM or other operator with other fields in the SELECT statement nothing work !

Where is a documentation for using SQL syntax with ADO and MDB database on delphi 5?


Thanks.
ASKER CERTIFIED SOLUTION
Avatar of kretzschmar
kretzschmar
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DavidRissato
DavidRissato

Your problem is due to the incorrect use of SQL aggregations.

When you use any of the aggregation functions (sum(), count(), min(), max() etc), the SQL parser tries to find which fields you expect data to be aggregated.

Suppose you have the folowing table STOCK:

Category   Product     Price    StockQty
School     Pen         0,50       120  
School     Pencil      0,20        50  
School     Ruler       0,20        50  
Kitchen    Fork        0,80        30  
Kitchen    Napkins     0,20      1000  
Kitchen    Knife       1,10      1000  

If you want to count how many items you have in stock (regardless of its kind) you do:

SELECT SUM(StockQty) FROM Stock

and it returns 2250

Now, suppose you want to know how many items of each Category you have in stock:

SELECT Category, SUM(StockQty) As Qty FROM Stock GROUP BY Category

and it returns
Category   Qty
School     220
Kitchen    2030

You would think "I've just replicated the fields of my select on the GROUP BY clause. It seems so stupid. Why have them invented this annoying GROUP BY clause?"

Well, suppose you want the maximal praticated costs on your stock. You know you must respect their category on this calculation, but at this time the name of the category is not important for you:

SELECT MAX(Price) As MaxPrices
FROM Stock
GROUP BY Category

It will return
MaxPrices
0,80
1,10

With these values, your program could make a custom average formula and conclude anything it wants.

I hope it helps you to understand GROUP BY clause. If you have time, read about HAVING clause. It works together with GROUP BY clause and give you nice results like the following example:

Now, we want to know what categories have less than 300 items in stock:
 
SELECT Category, SUM(StockQty) As Qty
FROM Stock
GROUP BY Category
HAVING SUM(StockQty) < 300

It will return
Category     Qty
School       220

Nice uh?

Well, hope this explanation helps you.

{}'s
David Rissato Cruz
Avatar of lgc5800

ASKER

Thanks kretzschmar and davidrissato

But, kretzschmar when i try your sql, delphi say :
 
'field lastname not found'

Syntax problem ?

i try too :  count([ado_persons.lastname]) but don't work.

use
count(*)
instead

can you tell what you want to archive with this statement?
because you using an aggregation functions such count(),sum,max ...etc you should use GROUP BY in SQL

add this part to your SQL

group by ado_persons.firstname, ado_persons.country, ado_persons.grade