SQL Aggreate函数

Question

我有一张表，上面列出了一些案例，并指派了初级和中级技术人员。我想要完成的是将一名技术人员作为初级和二级技术工作的案例数量进行汇总。应该像这样..。

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

我从表中提取的数据如下：

CaseID, PrimaryTech, SecondaryTech, DOS

过去我用过这样的方法，但现在我的上级也在问次要案件的数量.

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

我做了一些研究，但似乎找不到我的问题的答案。

Answer 1

您可以将两个子查询与一个完全连接组合在一起，如本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;

Answer 2

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

Answer 3

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