SQL Aggreate Functions

Question

I have table which list a number of cases and assigned primary and secondary technicians. What I am trying to accomplish is to aggregate the number of cases a technician has worked as a primary and secondary tech. Should look something like this...

Technician     Primary     Secondary
John             4              3
Stacy            3              1
Michael          5              3

The table that I am pulling that data from looks like this:

CaseID, PrimaryTech, SecondaryTech, DOS

In the past I have used something like this, but now my superiors are asking for the number of secondary cases as well...

SELECT PrimaryTech, COUNT(CaseID) as Total
GROUP BY PrimaryTech

I've done a bit of searching, but cant seem to find the answer to my problem.

juergen d · Accepted Answer · 2013-11-11 19:15:52Z

1

Select Tech, 
       sum(case when IsPrimary = 1 then 1 else 0 end) as PrimaryCount,
       sum(case when IsPrimary = 0 then 1 else 0 end) as SecondaryCount
from
(
  SELECT SecondaryTech as Tech, 0 as IsPrimary
  FROM your_table
  union all
  SELECT PrimaryTech as Tech, 1 as IsPrimary
  FROM your_table
) x
GROUP BY Tech

answered Nov 11, 2013 at 19:15

juergen d

205k40 gold badges304 silver badges377 bronze badges

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

3 Comments

user2891487 Over a year ago

thank you for the prompt response and the clean up. The issue is that the Primary and Secondary tech fields are varchar's. So I want to group by the Primary tech field then sum the number of rows in which they fall under the secondary tech field...

Hart CO Over a year ago

That's what this query does.

Registered User Over a year ago

This approach will return a NULL technician if there is a primary and no secondary. This can be avoided by adding WHERE PrimaryTech/SecondaryTech IS NOT NULL to there subquery.

Registered User · Accepted Answer · 2013-11-11 19:25:33Z

0

You can group two subqueries together with a FULL JOIN as demonstrated in this SQLFiddle.

SELECT Technician = COALESCE(pri.Technician, sec.Technician)
    , PrimaryTech
    , SecondaryTech
FROM 
    (SELECT Technician = PrimaryTech
        , PrimaryTech = COUNT(*)
    FROM Cases
    WHERE PrimaryTech IS NOT NULL
    GROUP BY PrimaryTech) pri
FULL JOIN
    (SELECT Technician = SecondaryTech
        , SecondaryTech = COUNT(*)
    FROM Cases
    WHERE SecondaryTech IS NOT NULL
    GROUP BY SecondaryTech) sec
    ON pri.Technician = sec.Technician
ORDER By Technician;

answered Nov 11, 2013 at 19:25

Registered User

8,4238 gold badges54 silver badges67 bronze badges

Comments

Santhosh · Accepted Answer · 2013-11-11 19:35:23Z

0

SELECT COALESCE(A.NAME, B.NAME) AS NAME, CASE WHEN A.CASES IS NOT NULL THEN A.CASES ELSE 0 END AS PRIMARY_CASES,
CASE WHEN B.CASES IS NOT NULL THEN B.CASES ELSE 0 END AS SECONDARY_CASES
FROM
(
SELECT COUNT(*) AS CASES, PRIMARYTECH AS NAME FROM YOUR_TABLE
GROUP BY PRIMARYTECH
) AS A
FULL OUTER JOIN
(
SELECT COUNT(*) AS CASES, SECONDARYTECH AS NAME FROM YOUR_TABLE
GROUP BY SECONDARYTECH
) AS B
ON A.NAME = B.NAME

answered Nov 11, 2013 at 19:35

Santhosh

1,8011 gold badge16 silver badges26 bronze badges

Collectives™ on Stack Overflow

SQL Aggreate Functions

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related