?
Solved

SQL PROBLEM

Posted on 2003-02-28
5
Medium Priority
?
164 Views
Last Modified: 2013-11-23
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.
0
Comment
Question by:lgc5800
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 27

Accepted Solution

by:
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 ;-)

0
 
LVL 1

Expert Comment

by:DavidRissato
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:

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
 

Author Comment

by:lgc5800
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.

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8047380
use
count(*)
instead

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

Expert Comment

by:hetturki
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

777 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