SQL Query, using COUNT and AVERAGES MS SQL Server Management Studio

I'm fairly new to SQL, I would like some help on this query please. Here is my script so far. Let me know if you need further information,

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

But what I would like to see is 开发者_如何学编程this please. A total and an average per day for each inspector so Joe Bloggs has done a total of 99 inspections and then averages the total by how many a certain amount of days i.e this is 4 days worth or inspection.which works out as 24.75.Is this possible?

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        

First, inner "prequery" will get counts per day... THEN use THAT as basis to get total SUM() and AVG(). This will return one row per inspector and NOT all the individual days shown in your breakdown.

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

If you really want each individual row per day by the inspector, you would basically have to add other magic to join with the same INNER "PreQuery" to get the individual day, and real magic to only show the total and average on the first row per inspector.

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