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.
lgc5800Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
use the group by clause for non aggregate fields like


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

docu you may find in the msaccess helpfile
keyword sql or select

meikl ;-)




meikl ;-)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidRissatoCommented:
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
0
lgc5800Author Commented:
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.

0
kretzschmarCommented:
use
count(*)
instead

can you tell what you want to archive with this statement?
0
hetturkiCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.