Count Error! (1 Viewer)

[bapolis]

Hello,

I have a table with the following fields:

- id
- name

In my query, I want to display the values of both of these fields for each row, but also display how many times each unique name appears. For example, how many rows have the name "Bob". To do this, I'm using the COUNT function, but it's not working! Here is my SQL statement:

SELECT DISTINCT [id], [name], COUNT([bid]) AS total FROM people;

When I run this query, I get the following error:

"You tried to execute a query that does not include the specified expression 'id' as a part of an aggregate function

The result should look like this:

id - name - total
--------------------------------
1 - Bob - 2
2 - Dan - 4
3 - Eric - 1

Can anyone help me with this?

 

[DBL]

Quick point that won't solve your problems but Name is a reserved word in Access so you shouldn't use it as a field name. Search the forum or Access help for more info on reserved names.

Open a query in design view and add the people table. Take over the name field and the id field. Make sure the Totals button on the menu bar is depressed. In the totals row select GroupBy for the Name field and Count for the ID field and see how you get on.

 

[bapolis]

I followed your directions, and this is the query it built:

SELECT Count(ram.bid) AS CountOfbid, ram.building_name, [total] AS Expr1
FROM ram
GROUP BY ram.building_name, [total];

obviously this isn't valid. How do I define what total is?

 

[DBL]

Here's one I've taken from a table I have with names and id fields. I've grouped by name and counted the number of ids

SELECT tblUsers.UFirstName, Count(tblUsers.UserID) AS CountOfUserID
FROM tblUsers
GROUP BY tblUsers.UFirstName;

If you're trying to do this in a crosstab query, I don't think you need to, a select query would do the job.

 

[bapolis]

ok, we're making progress! The query is counting right, but when I try to select bid so I can display it, I get the error again, here is the statement that works (but doesn't display id):

SELECT DISTINCT Count(id) AS total, name
FROM people
GROUP BY name

now, this is the statement I'm trying to use in order to display the id in the results!

SELECT DISTINCT Count(id) AS total, name, id
FROM people
GROUP BY name

it's very important that the id is displayed, but no matter where I put it in the SELECT clause, I get the same error!

Any ideas? I appreciate your help!

 

[DBL]

I might be wrong here but you can't display the individual IDs and group by the names at the same time. Taking out the calcualtion bit, if you grouped by individual id and name you would get an individual result for every record in the table because the ID is unique and therefore can't be placed in any group. You can group by names because they aren't unique and you can count how many IDs have the same name.

Maybe I'm on the wrong track here.

Something else I've just thought of, maybe you are trying to count the IDs with identical names and then total further. If that's the case, you'll need to create a second query and use the first query as the basis of the total query.

 

[Pat Hartman]

You need to group by ALL the non-aggregate fields:

SELECT Count(id) AS total, name, id
FROM people
GROUP BY name, id;

 

[bapolis]

wow, thanks for the help. someone actually gave me the following SQL that works:

sql = "SELECT DISTINCT A.building_name, A.bid, B.total " &_
"FROM (SELECT building_name, bid FROM ram) AS A " &_
"INNER JOIN (SELECT building_name, count(bid) AS total FROM ram " &_
"WHERE fm LIKE '" & username & "' " &_
"GROUP BY building_name) AS B ON A.building_name = B.building_name"

but your statement is much more conceis! thanks!