Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium



Posted on 2003-02-28
Medium Priority
Last Modified: 2013-11-23

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?

Question by:lgc5800
LVL 27

Accepted Solution

kretzschmar earned 600 total points
ID: 8042648
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 ;-)


Expert Comment

ID: 8045664
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:


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

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.

David Rissato Cruz

Author Comment

ID: 8047286
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.

LVL 27

Expert Comment

ID: 8047380

can you tell what you want to archive with this statement?

Expert Comment

ID: 8126843
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question