CODEWITHSUNDEEP
sql
user6906550
user6906550

Reputation:

Multiple Group By and Count

I was wondering if someone could help me with this. Here is a sample set of my data:

FirstName   LastName   Department  Ticket  Hours Shift   DateWorked   Key
Bob         Smith      Sleeves     23235    4     1     2017-01-01    001
Bob         Smith      Sleeves     12345    4     1     2017-01-01    001    
Jim         Bo         Sleeves     12345    8     1     2017-01-01    002
Janet       Moore      Lids        78945    8     2     2017-01-01    003
Jon         Bob        Lids        45621    1.5   3     2017-01-01    004
Jon         Bob        Lids        45621    7.5   3     2017-01-01    004

Bob         Smith      Mugs        12345    8     1      2017-01-02
Jim         Bo         Lids        99999    8     3      2017-01-02

It should return something like this:

DateWorked   Shift    Department  HeadCount
2017-01-01   1        Sleeves     2  (Bob Smith has two entries but counted as one and Jim Bo makes for 2)
2017-01-01   2        Lids        1  (Janet)
2017-01-01   3        Lids        1  (Jon)

Please note that all departments work all shifts. This is just a sample set. There can be anywhere from none to a hundred per department.

Also one employee could work multiple departments in one day! I don't know how to account for that.

This is what I have. So for this example it's not summing Bob Smith. It's counting him as two. 

SELECT Scheduled, Department, [Shift], COUNT(*) as HeadCount
FROM EmployeeTickets
WHERE  Scheduled >= '2017-01-01' AND Scheduled < '2017-12-31'
GROUP BY Scheduled, Department, [Shift]
ORDER BY Scheduled, Department, [Shift]

Thank you.

ETA I don't know if it helps but in the table there is a key per entry, so Bob Smith on Jan 1 would have a key for that day. His social security number is also in there. I'm trying to group by one of those somehow.

Upvotes: 0

Views: 44

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

just use DISTINCT 

SELECT Scheduled, Department, [Shift], COUNT( DISTINCT FirstName ) as HeadCount
FROM EmployeeTickets
WHERE  Scheduled >= '2017-01-01' AND Scheduled < '2017-12-31'
GROUP BY Scheduled, Department, [Shift]
ORDER BY Scheduled, Department, [Shift]

Of course this will have problem if you have multiple persons with same name. So I hope you have some EmployeeID on your tables, so you can differentiate every employee.

COUNT(DISTINCT EmployeeID)

Upvotes: 1

Related Questions