How to Sort Query Output Using a Counted Field?

[Stolman]

I created two queries against my ACTIVITY table:

“qryActivity0”
SELECT Activity.Zip, Activity.Cat, Activity.SubCat, Activity.Office, Activity.Date
FROM Activity
ORDER BY Activity.Zip;

“qryActivity1”
SELECT qryActivity0.Zip, Count(*) AS CountOfZips
FROM qryActivity0
WHERE (((qryActivity0.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
ORDER BY CountOfZips;

When I try to ORDER BY CountOfZips, I get an error message: “You tried to execute a query that does not include the specified expression ‘Zip’ as part of an aggregate function.” NOTE: in “qryActivity1” when I replace the ORDER BY statement with "GROUP BY qryActivity0.Zip;", I obtain correct results but the results are sorted by Zip and not by CountOfZip. CountOfZip sorting would enable me to SELECT TOP 3 Zips in a subsequent query.

Question 1: What coding is required to create output from “qryActivity1” that is sorted in Descending order on CountOfZips? Is it even possible?

Related Item: When I try to SELECT any additional “qryActivity0” fields (e.g., ‘qryActivity0.Cat’) in the SELECT statement of “qryActivity1”, I get an error message: “You tried to execute a query that does not include the specified expression ‘Cat’ as part of an aggregate function.”

Question 2: What coding is required to SELECT these additional fields in “qryActivity1” or is this simply not possible?

Thank you,
-bill

 

[lespaul]

try replacing “qryActivity1” with this:


SELECT qryActivity0.Zip, Count(*) AS CountOfZips
FROM qryActivity0
WHERE (((qryActivity0.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
GROUP BY qryActivity0.Zip
ORDER BY Count(*);


Leslie

 

[PHV]

sorted in Descending order on CountOfZips
SELECT Zip, Count(*) AS CountOfZips
FROM qryActivity0
WHERE [Date] Between [Enter Beginning Date] And [Enter Ending Date]
ORDER BY 2 DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Stolman]

Thanks Leslie and PH for the lightning-quick replies! (I have been trying different approaches since midnight US/MST to no avail).

Leslie: yours works, many thanks. Can you recommend a book on SQL examples that I might reference?

PH: your suggestion, while more concise, still gives me the error message: “You tried to execute a query that does not include the specified expression ‘Zip’ as part of an aggregate function.” (Can you also recommend a book?)

BOTH: Any suggestions on what coding is required to SELECT additional fields in the SELECT statement of “qryActivity1” (e.g., ‘SELECT qryActivity0.Zip, Count(*) AS CountOfZips, qryActivity0.office’)without getting the error message: “You tried to execute a query that does not include the specified expression ‘Cat’ as part of an aggregate function.” - or is this simply not possible?

Sincere thanks to you both,
-b

 

[lespaul]

The reason that PHV's solution gave the same error is because he didn't add the GROUP BY clause.

When you use aggregate functions (like COUNT, SUM, AVG) you have to Group the results. Every field that isn't an aggregate need to be included in the SELECT.

If you want to add Cat to the select clause, you have to add it to the GROUP BY clause:

SELECT qryActivity0.Zip, Cat, Count(*) AS CountOfZips
FROM qryActivity0
WHERE (((qryActivity0.Date) Between [Enter Beginning Date] And [Enter Ending Date]))
GROUP BY qryActivity0.Zip, Cat
ORDER BY Count(*);



Leslie

 

[Stolman]

MANY thanks Leslie. Great Advice! Very much appreciate your skill, not to mention your patience! Works like a champ!

Most Sincerely,
-bill

 

[lespaul]

glad to help!

leslie