aggregate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I used the COUNT function in a query to count the number of fields. I gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select COUNTOF and I
get an error message telling me that I tried to execute a query the doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?
 
Hi Brenda,

I don't suppose you could post your SQL? Did you say you are counting
fields? Did you mean rows (records)?

Any time you use an aggregate function in a query you must "group" the
non-aggregated fields. Lets say you had 3 fields in your query:

[Order Period], [Product Name] and [Quantity Ordered]. [Quantity Ordered]
is the aggregated field. You would structure the SQL something like this:

--NB: This SQL not tested

SELECT Format(OrderDate, "YYYY/MM") AS [Order Period],
ProductName AS Product,
COUNT(*) AS [Quantity Ordered]
FROM ((Orders AS O INNER JOIN [Order Details] AS OD ON O.OrderID =
OD.OrderID)
INNER JOIN Products P ON OD.ProductID = P.ProductID)
GROUP BY Format(OrderDate, "YYYY/MM") , ProductName

The number of rows are counted for each change in Period and Product Name.
Do not include the alias names (i.e. [Order Period], etc) in the GROUP BY
clause.

Jamie
 
This is my first query
SELECT LEFT(ZIP,5) AS UNIZIP, COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5);

This is the query I'm getting the error message on.
SELECT LEFT(UNIZIP,3) AS ZIP3, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE UNIZIP NOT IN
(SELECT UNIZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP >15
GROUP BY UNIZIP)
GROUP BY LEFT(UNIZIP,3)
UNION ALL
SELECT UNIZIP, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP > 15
GROUP BY UNIZIP;
 
This is my first query:
SELECT LEFT(ZIP,5) AS UNIZIP, COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5);
Here is the query I'm getting the error message on:
SELECT LEFT(UNIZIP,3) AS ZIP3, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE UNIZIP NOT IN
(SELECT UNIZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP >15
GROUP BY UNIZIP)
GROUP BY LEFT(UNIZIP,3)
UNION ALL
SELECT UNIZIP, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP > 15
GROUP BY UNIZIP;

Jamie Richards said:
Hi Brenda,

I don't suppose you could post your SQL? Did you say you are counting
fields? Did you mean rows (records)?

Any time you use an aggregate function in a query you must "group" the
non-aggregated fields. Lets say you had 3 fields in your query:

[Order Period], [Product Name] and [Quantity Ordered]. [Quantity Ordered]
is the aggregated field. You would structure the SQL something like this:

--NB: This SQL not tested

SELECT Format(OrderDate, "YYYY/MM") AS [Order Period],
ProductName AS Product,
COUNT(*) AS [Quantity Ordered]
FROM ((Orders AS O INNER JOIN [Order Details] AS OD ON O.OrderID =
OD.OrderID)
INNER JOIN Products P ON OD.ProductID = P.ProductID)
GROUP BY Format(OrderDate, "YYYY/MM") , ProductName

The number of rows are counted for each change in Period and Product Name.
Do not include the alias names (i.e. [Order Period], etc) in the GROUP BY
clause.

Jamie

Brenda @ AIM said:
I used the COUNT function in a query to count the number of fields. I gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select COUNTOF and
I
get an error message telling me that I tried to execute a query the
doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?
Click to expand...
Click to expand...
 
I realize now that I need to put COUNTOFZIP in my GROUPBY clause. But if I
write it like this (GROUP BY UNIZIP, COUNTOFZIP) then I have multiple
instances of my UNIZIP in the result. I need these grouped together. If i
write it this way (GROUP BY COUNTOFZIP, UNIZIP) then it is grouped by the
count(ascending). I need it grouped by UNIZIP (ascending)>
 
Get it to be in the order you want by using an Order By clause at the end of
the second query.
 
Back
Top