lgc5800
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.
i have a problem with this sql :
SELECT count(ado_persons.lastname
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.lastnam e]) but don't work.
But, kretzschmar when i try your sql, delphi say :
'field lastname not found'
Syntax problem ?
i try too : count([ado_persons.lastnam
use
count(*)
instead
can you tell what you want to archive with this statement?
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
add this part to your SQL
group by ado_persons.firstname, ado_persons.country, ado_persons.grade
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