SQL Server 2005，统计总数

Question

我想知道如何查询一个数据库，我可以通过它返回两个总和，第一个是检查的总数，然后是每个检查员的总数，即 Joe 总共做了 4 个检查，但仍然显示了不同的分割水平。我是 sql 的新手，所以到目前为止已经包含了我的脚本，但如果您需要更多信息，请告诉我。

SELECT     InspectorCode AS Inspector, InspectionProcedureName AS [Procedure], COUNT(*) AS     Total
FROM         UnitData_Vehicle
WHERE     (DATEDIFF(day, InspectionDateTime, GETDATE()) = 1)
AND InspectionProcedureName LIKE '%Inspection%'
GROUP BY InspectionProcedureName, InspectorCode

我想看看

Inspector   Procedure   Total    InspTotal 
joe         1 - Inspection  1        4
joe         2 - Inspection  3
kit         3 - Inspection  14       14
hugh        1 - Inspection  17       32
hugh        6 - Inspection  15
mike        4 - Inspection  18       18

Answer 1

我不确定 sql server，但在 oracle 中，以下查询会给出你想要的结果 -

SELECT zz.Inspector as Inspector,
       zz.Procedure as Procedure,
       zz.total as total,
       case 
       when zz.c_1 =1 then
       zz.InspTotal 
       else 
       null
       end as InspTotal        
       FROM (SELECT InspectorCode AS Inspector, 
       InspectionProcedureName AS Procedure, 
       COUNT(*) AS     Total ,
       count(*) over(partition by InspectorCode) InspTotal,
       row_number() over( partition by InspectorCode order by InspectionProcedureName) c_1 
FROM   UnitData_Vehicle 
WHERE  (DATEDIFF(day, InspectionDateTime, GETDATE()) = 1) 
       AND InspectionProcedureName LIKE '%Inspection%' 
GROUP BY InspectionProcedureName, InspectorCode)zz;

请检查您的 where健康)状况..
我只是在检查和工作最后一个想要的列..
就像在你的问题中一样，你没有告诉任何关于条件的事情......

如果您想对此查询中的任何内容进行澄清，请在此处发表评论..

还有一件事 -
我假设对于任何 InspectorCode 代码，您只需要一次 InspTotal 列值。
我对吗？？