CODEWITHSUNDEEP
sqlsql-serversql-server-2008
M.Pratt_460
M.Pratt_460

Reputation: 1

SQL: multiple counts from same table

I am having a real problem trying to get a query with the data I need. I have tried a few methods without success. I can get the data with 4 separate queries, just can't get hem into 1 query. All data comes from 1 table. I will list as much info as I can.

My data looks like this. I have a customerID and 3 columns that record who has worked on the record for that customer as well as the assigned acct manager

 RecID_Customer___CreatedBy____LastUser____AcctMan

 1-------1374----------Bob Jones--------Mary Willis------Bob Jones

 2-------1375----------Mary Willis------Bob Jones--------Bob Jones

 3-------1376----------Jay Scott--------Mary Willis-------Mary Willis

 4-------1377----------Jay Scott--------Mary Willis------Jay Scott

 5-------1378----------Bob Jones--------Jay Scott--------Jay Scott

I want the query to return the following data. See below for a description of how each is obtained.

Employee___Created__Modified__Mod Own__Created Own 

 Bob Jones--------2-----------1---------------1----------------1

 Mary Willis------1-----------2---------------1----------------0

 Jay Scott--------2-----------1---------------1----------------1

Created = Counts the number of records created by each Employee

Modified = Number of records where the Employee is listed as Last User (except where they created the record)

Mod Own = Number of records for each where the LastUser = Acctman (account manager)

Created Own = Number of Records created by the employee where they are the account manager for that customer

I can get each of these from a query, just need to somehow combine them:

Select CreatedBy, COUNT(CreatedBy) as Created
FROM [dbo].[Cust_REc] GROUP By CreatedBy

Select LastUser, COUNT(LastUser) as Modified
FROM [dbo].[Cust_REc] Where LastUser != CreatedBy GROUP By LastUser

Select AcctMan, COUNT(AcctMan) as CreatePort
FROM [dbo].[Cust_REc] Where AcctMan = CreatedBy GROUP By AcctMan

Select AcctMan, COUNT(AcctMan) as ModPort
FROM [dbo].[Cust_REc] Where AcctMan = LastUser AND NOT AcctMan = CreatedBy GROUP By AcctMan

Can someone see a way to do this? I may have to join the table to itself, but my attempts have not given me the correct data.

Upvotes: 0

Views: 3013

Answers (4)

SlimsGhost
SlimsGhost

Reputation: 2909

The following will give you the results you're looking for.

select 
    e.employee,
    create_count=(select count(*) from customers c where c.createdby=e.employee),
    mod_count=(select count(*) from customers c where c.lastmodifiedby=e.employee),
    create_own_count=(select count(*) from customers c where c.createdby=e.employee and c.acctman=e.employee),
    mod_own_count=(select count(*) from customers c where c.lastmodifiedby=e.employee and c.acctman=e.employee)
from (
    select employee=createdby from customers
    union
    select employee=lastmodifiedby from customers
    union
    select employee=acctman from customers
) e

Note: there are other approaches that are more efficient than this but potentially far more complex as well. Specifically, I would bet there is a master Employee table somewhere that would prevent you from having to do the inline view just to get the list of names.

Upvotes: 1

Andrew L.
Andrew L.

Reputation: 260

I had the same issue with the Modified column. All the other columns worked okay. DCR example would work well with the join on an employees table if you have it.

SELECT  CreatedBy AS [Employee],
        COUNT(CreatedBy) AS [Created],
        --Couldn't get modified to pull the right results
        SUM(CASE WHEN LastUser = AcctMan THEN 1 ELSE 0 END) [Mod Own],
        SUM(CASE WHEN CreatedBy = AcctMan THEN 1 ELSE 0 END) [Created Own]
FROM Cust_Rec
GROUP BY CreatedBy

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15155

This highly inefficient query may be a rough start to what you are looking for. Once you validate the data then there are things you can do to tidy it up and make it more efficient.

Also, I don't think you need the DISTINCT on the UNION part because the UNION will return DISTINCT values unless UNION ALL is specified.

SELECT  
    Employees.EmployeeID,
    Created   =(SELECT COUNT(*) FROM Cust_REc WHERE Cust_REc.CreatedBy=Employees.EmployeeID),
    Mopdified =(SELECT COUNT(*) FROM Cust_REc WHERE Cust_REc.LastUser=Employees.EmployeeID AND Cust_REc.CreateBy<>Employees.EmployeeID),
    ModOwn    =
        CASE WHEN NOT Empoyees.IsManager THEN NULL ELSE
            (SELECT COUNT(*) FROM Cust_REc WHERE AcctMan=Employees.EmployeeID)
        END,
    CreatedOwn=(SELECT COUNT(*) FROM Cust_REc WHERE AcctMan=Employees.EmployeeID AND CReatedBy=Employees.EMployeeID)
FROM
    (       
        SELECT
            EmployeeID,
            IsManager=CASE WHEN EXISTS(SELECT AcctMan FROM CustRec WHERE AcctMan=EmployeeID)
        FROM
        (
            SELECT DISTINCT 
                EmployeeID
            FROM
            (
                SELECT EmployeeID=CreatedBy FROM Cust_Rec
                UNION
                SELECT EmployeeID=LastUser FROM Cust_Rec
                UNION
                SELECT EmployeeID=AcctMan FROM Cust_Rec
            )AS Z
        )AS Y
    )
    AS Employees

Upvotes: 0

DCR
DCR

Reputation: 15647

this seems pretty straight forward. Try this:

select a.employee,b.created,c.modified ....
from (select distinct created_by from data) as a
inner join
     (select created_by,count(*) as created from data group by created_by) as b
on a.employee = b.created_by)

inner join ....

Upvotes: 0

Related Questions