CODEWITHSUNDEEP
mysqlsql
John G
John G

Reputation: 33

SQL How to count unique value in a column only once per person per day

I have an SQL table the displays names of people who scan to a bar code. They often scan to multiple bar codes per day and sometimes the same code on the same day. I need to count the amount of scan types for each person each day. The problem I am having is that I don't want to count the same bar code value for the one person each day.

Below is an example of the SQL table.

Example SQL Table

Here is my SQL

SELECT EVENTDATE, BARCODE, count(BARCODE) AMOUNT
from TIMESHEET
where EVENTDATE = '27-OCT-17' 
group by EVENTDATE, BARCODE
order by EVENTDATE, BARCODE

The result gives me a count of 5 but I want only 4 because there are two duplicate bar code scans for JOHN SMITH for the same day.

Upvotes: 2

Views: 4620

Answers (3)

Zorkolot
Zorkolot

Reputation: 2017

The reason the example query doesn't work is because you don't remove duplicates by name. To do this you have to take distinct values with name or group by everything in select statement. For example, subquery the distinct combinations of EVENTDATE, NAME, BARCODE to remove duplicates - then count barcodes by eventdate.

SELECT dT.EVENTDATE
      ,dT.BARCODE
      ,COUNT(dT.BARCODE) AS TOTAL
  FROM (
         SELECT DISTINCT EVENTDATE, NAME, BARCODE
         FROM TIMESHEET
         --WHERE EVENTDATE = '2017-10-27'  --if you really need to filter by date
                                           --otherwise will group by all dates
       ) AS dT
GROUP BY dT.EVENTDATE  
        ,dT.BARCODE

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Just count the DISTINCT names instead.

SELECT EVENTDATE, BARCODE, count(DISTINCT NAME) AMOUNT
from TIMESHEET
where EVENTDATE = '27-OCT-17' 
group by EVENTDATE, BARCODE
order by EVENTDATE, BARCODE;

But you should use some name_id instead of name because you can have 2 persons name Jhon Smith

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Perhaps you just want:

select EVENTDATE, NAME, count(DISTINCT BARCODE) as cnt
from TIMESHEET
where EVENTDATE = '27-OCT-17' 
group by EVENTDATE, NAME
order by EVENTDATE, NAME;

Upvotes: 1

Related Questions