August 26, 2015 at 4:15 am
Dear Everyone
I have the following query:
select
DateDiff(s,min(CONVERT(VARCHAR(8),TRANSIT_DATE,108)),max(CONVERT(VARCHAR(8),TRANSIT_DATE,108)))/3600 ,
count (distinct identifier),
parameter_3
from HA_TRANSIT
where transit_Date>= '2015-08-23 00:00:00.000' and transit_date <= '2015-08-24 00:00:00.000'
and identifier like '%C%'
--and identifier not like '%T%'
and identifier not like '%V%'
and identifier not like '%O%'
and parameter_3 = 'Filicori- Canteen'
group by parameter_3, identifier
The result is below:
Time Diff EmployeeCnt Company
9 1 Filicori- Canteen
8 1 Filicori- Canteen
8 1 Filicori- Canteen
5 1 Filicori- Canteen
Now i can modify my query and get the result:
Sum EmployeeTotal Company
94 Filicori- Canteen
I need the total man hours per company with the total employees per company. So the number 9 isnt the total but the first line. I need the total to say
9+8+8+5=30
I tried derived tables but it didnt work
let me know if anyone has any ideas
thanks
K.
But i need the total
August 26, 2015 at 4:24 am
SELECT SUM(UnnamedColumn1) AS Total,
SUM(UnnamedColumn2) AS EmployeeCnt,
Parameter_3
FROM (SELECT DATEDIFF(s, MIN(CONVERT(VARCHAR(8), TRANSIT_DATE, 108)), MAX(CONVERT(VARCHAR(8), TRANSIT_DATE, 108))) / 3600 AS UnnamedColumn1,
COUNT(DISTINCT identifier) AS UnnamedColumn2,
parameter_3
FROM HA_TRANSIT
WHERE transit_Date >= '2015-08-23 00:00:00.000' AND transit_date <= '2015-08-24 00:00:00.000' AND identifier LIKE '%C%'
--and identifier not like '%T%'
AND identifier NOT LIKE '%V%' AND identifier NOT LIKE '%O%' AND parameter_3 = 'Filicori- Canteen'
GROUP BY parameter_3,
identifier
) sub
GROUP BY Parameter_3
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2015 at 4:59 am
I see so you used a derived table with two sums.
Do you mind explaining why you would use two sums?
What is the logic so i can understand for next time?
thanks
K.
August 26, 2015 at 5:31 am
I'm using two SUMs because you want two columns summed. The first (9+8+8+5) = 30, the second (1+1+1+1) = 4.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply