SQL查询，使用计数和平均值MS SQL Server Management Studio

Question

我是SQL的新手，我想要一些关于这个查询的帮助。这是到目前为止我的脚本。如果你需要更多信息，请告诉我，

SELECT DISTINCT EstimName_String AS Inspector, InspectionProcedureName AS [Inspection         Procedure Name], COUNT(*) AS InspectionsDone
FROM         UnitData_Vehicle
WHERE     (InspectionProcedureName LIKE '%Inspection%')
where datediff(day, phodat_datetime, getdate()) >= 1
and datediff(day, phodat_datetime, getdate()) <= 4
GROUP BY InspectionProcedureName, EstimName_String
ORDER BY Inspector

但我希望看到的是这一点。每个检查员的总数和平均每天，因此Joe Bloggs总共做了99次检查，然后将总数除以一定天数的平均值，即这是4天的价值或inspection.which计算为24.75。这是可能的吗？

Inspector   Inspection Procedure Name   Inspections done    Total   Avg per Day
Joe Bloggs  Inspection                        16            99.00   24.75
Joe Bloggs  Inspection                        1     
Joe Bloggs  Inspection                        4     
Joe Bloggs  Inspection                        78        
Jack sprat  Inspection                        14            87.00    21.75
Jack sprat  Inspection                        73        
Humpty Dumpty   Inspection                    7             75.00    18.75
Humpty Dumpty   Inspection                    68        
Micky Mouse Inspection                        13            80.00     20
Micky Mouse Inspection                        67        
Jack Jill   Inspection                        11            76.00     19
Jack Jill   Inspection                        1     
Jack Jill   Inspection                        64        

Answer 1

首先，内部的"prequery“将获得每天的计数……然后以此为基础得到total SUM()和AVG()。这将返回每个检查员一行，而不是您的明细中显示的所有单独的日期。

select 
      InspectionProcedureName AS [Inspection         Procedure Name], 
      EstimName_String AS Inspector, 
      sum( InspectionsDone ) as InspectionsDone,
      avg( InspectionsDone ) as AvgInspections
   from 
      ( SELECT 
            InspectionProcedureName, 
            EstimName_String AS Inspector, 
            datediff(day, phodat_datetime, getdate()) as ByDate,
            COUNT(*) AS InspectionsDone
         FROM
            UnitData_Vehicle
         WHERE     
                InspectionProcedureName LIKE '%Inspection%'
            AND datediff(day, phodat_datetime, getdate()) >= 1
            and datediff(day, phodat_datetime, getdate()) <= 4
         GROUP BY 
            InspectionProcedureName, 
            EstimName_String,
            datediff(day, phodat_datetime, getdate()) ) PreQuery
   ORDER BY 
      Inspector

如果你真的希望检查员每天检查每一行，你基本上必须添加其他魔术来连接相同的内部"PreQuery“来获得独立的一天，并且真正的魔术只显示每个检查员第一行的总数和平均值。

Answer 2

SELECT EstimName_String AS Inspector, 
InspectionProcedureName AS [Inspection Procedure Name], 
sum(InspectionsDone) AS Total, 
cast(sum(InspectionsDone) as float)/count(*) as [Avg Per Day]
FROM         UnitData_Vehicle
GROUP BY InspectionProcedureName, EstimName_String