CODEWITHSUNDEEP
sqlsql-serversql-server-2005count
Tyrone2011
Tyrone2011

Reputation: 21

SQL Server 2005, counting totals

I would like to know how to query a database whereby I can return two sums, the first being the total number of inspections, then a total for each inspector ie, Joe has done a total of 4 inspections but still shows a breakdown of the different levels. I'm new to sql so have included my script so far, but let me know if you need more info.

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

I would like to see

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

Upvotes: 2

Views: 294

Answers (3)

niktrs
niktrs

Reputation: 10066

The following example code runs fine for my database

SELECT fiscalmonth,item,fiscalyear
       ,SUM(valuesale)
       ,
(SELECT SUM(valueSale)
FROM inbalance b 
WHERE b.fiscalmonth <= a.fiscalmonth 
and a.fiscalyear =b.fiscalyear 
and a.item = b.item
) AS RunningTotal
FROM inbalance a
WHERE a.item='1101'
AND a.fiscalyear=2011
GROUP BY fiscalmonth,item,fiscalyear

So by "translating" my code to your query

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

Upvotes: 0

Andomar
Andomar

Reputation: 238078

EDIT: Based on @pratik garg's answer, looks like this works in SQL Server:

select  InspectorCode as Inspector
,       InspectionProcedureName as Procedure
,       count(*) as Total
,       count(*) over (partition by InspectorCode) as InspTotal
from    UnitData_Vehicle as uv1
where   (datediff(day, InspectionDateTime, getdate()) = 1)
        and InspectionProcedureName like '%Inspection%'
group by 
        InspectorCode
,       Procedure

The count(*) over (partition by InspectorCode) apparently honors the where clause, but does count over multiple groups. Interesting :)

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

I am not sure about sql server but in oracle following query will give result as you want-

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;

please check your where condition.. I was checking and working for last desired column only.. as in you question you have not tell anything about conditions...

please comment here if you want clarification on anything in this query..

one more thing - i assumed that you want InspTotal column value only once for any InspectorCode code.. am i right??

Upvotes: 2

Related Questions