Unique Values Count

Question

I have a small list of employees, and how much units they finished a given day. But I have duplicate values, because they have to place multiple components. List looks something like this:

Employee	SerialNumber	ACTION
x	111	PLACE
x	111	PLACE
x	222	PLACE
y	333	PLACE
y	333	PLACE
y	444	PLACE

I would like to get a query, to show the amount of units the employee made without duplicates, so like this:

X - 2

Y - 2

I made a query for it in MS Access:

SELECT Operator, COUNT(DISTINCT SerialNumber) as "QTY"
FROM Database
WHERE ACTION="PLACE"
GROUP BY Operator;

But it gives me the following error:

Syntax error (missing operator) in query expression 'COUNT(DISCTINCT SerialNumber)'.

What DBMS you are using? and please give us the complete schema of your table!! Consider updating your question to include your schema and DBMS you are using. — Emad Kerhily
– Emad Kerhily, Commented Jun 5 at 7:28

Black cat · Accepted Answer · 2025-06-05 10:59:09Z

0

Modify your query like this:

SELECT Employee, COUNT('DISTINCT Serialnumber') as 'QTY'
from Database1
where ACTION='PLACE'
GROUP BY Employee;

The error message caused by COUNT function requires a string expression as parameter. Also change the table name from Database to something else. It is a reserved word.

answered Jun 5 at 10:59

Black cat

7,6358 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2025-06-05 11:24:39Z

0

Use a subquery:

SELECT 
    Operator, 
    COUNT(*) As QTY
FROM
    (SELECT Operator FROM Database
    WHERE ACTION = "PLACE"
    GROUP BY Operator) As T
GROUP BY
    Operator

answered Jun 5 at 11:24

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

Unique Values Count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related