CODEWITHSUNDEEP
sqlsql-server
Loligans
Loligans

Reputation: 507

How to Select Count for every user within a table

I have a Table named "Calls". The table's columns are organized as

CallCounter, AssignedEmpID, CompletedByEmpID
1            200            200
2            200            200
3            201            200
4            200            200
5            201            201
6            201            200
7            200            200
8            200            200
9            200            201
10           201            201
...

How do I create a Select SQL query that will return the following data. Ideally I would like to complete the entire SQL query in 1 query. 

Employee    # Calls Assigned    # Calls Completed
200         6                   7
201         4                   3

I have tried the following queries

SELECT AssignedEmpID, COUNT(CallCounter) FROM CALLS
SELECT COUNT(*) FROM Calls

Upvotes: 0

Views: 383

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would do this with a union all and group by:

select EmpId, sum(assigned) as assigned, sum(completed) as completed
from ((select AssignedEmpID as EmpId, 1 as assigned, 0 as completed
       from calls
      ) union all
      (select CompletedByEmpID, 0, 1
       from calls
      )
     ) e
group by EmpId;

Another approach is full outer join:

select coalesce(a.EmpId, c.EmpId),
       coalesce(assigned, 0) as assigned, 
       coalesce(completed, 0) as completed
from (select AssignedEmpID as EmpId, count(*) as assigned
      from calls
      group by AssignedEmpID
     ) a full outer join
     (select CompletedByEmpID as EmpId, count(*) as completed
      from calls
      group by CompletedByEmpID
     ) c
     on a.EmpId = c.EmpId;

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

First you need to define a list of employees. You can do that with a subquery and union if you don't have an employees table to join with.

Then you could use conditional aggregation:

select t.empid, 
    sum(case when t.empid = c.AssignedEmpID then 1 else 0 end) AssignedCount,
    sum(case when t.empid = c.CompletedByEmpID then 1 else 0 end) CompletedCount
from (select distinct AssignedEmpID as empid from calls 
      union select distinct CompletedByEmpID from calls) t
        join calls c on t.empid in (c.AssignedEmpID, c.CompletedByEmpID)
group by t.empid

Upvotes: 2

Related Questions