CODEWITHSUNDEEP
sqlms-access-2010aggregate-functions
Toza
Toza

Reputation: 1358

Getting an SQL Error while combining aggregate function with list items

I've only started learning SQL recently. We're using MS Access (2010, in my case). No the task is that I have to list how many workers from a list called "radnik" work in different company locations (marked as "brod"). So I wrote this down:

SELECT brod, COUNT(*) AS count
FROM radnik
ORDER BY brod;

There are about 18 people in the list, all of them have values of 10,20,30,40 or 50, so it should make a table, for instance, like:

brod | count
10   | 5
20   | 9
...

I get, the error, though:
You tried to execute a query that does not include the specified expression 'brod' as part of an aggregate function.

Obivously, I'm doing something wrong, but I'm still very new to SQL. I'd very much appeciate if someone could explain to me why this doesn't work and how it could be fixed.

Upvotes: 1

Views: 59

Answers (2)

Mureinik
Mureinik

Reputation: 311808

You're missing the group by clause:

SELECT   brod, COUNT(*) AS count
FROM     radnik
GROUP BY brod
ORDER BY brod;

Upvotes: 1

Christian Specht
Christian Specht

Reputation: 36431

You need GROUP BY:

SELECT brod, COUNT(*) AS count
FROM radnik
GROUP BY brod
ORDER BY brod;

In short: whenever you're using an aggregate function like COUNT, all other selected columns need to be in the GROUP BY clause as well.

Upvotes: 2

Related Questions