January 6, 2010 at 4:12 am
Hi,
I have a query that returns the number of instances of a particular value. e.g. the number of times the number 7 appears in 5 particular columns.
Select employees.Name, Employees.Team,
SUM(CASE Data.Slot1 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot2 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot3 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot4 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot5 WHEN '7' THEN 1 ELSE 0 END) AS '1st Line'
FROM Data
INNER JOIN Employees ON Data.Name = Employees.ID
WHERE Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -1, getdate())), 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))
GROUP BY Employees.Name, Employees.Team
ORDER BY Employees.Team
This gives the result of 96.
This works great. What I need now is to take this number and divide it by 5. The query works as below:
Select employees.Name, Employees.Team,
(SUM(CASE Data.Slot1 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot2 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot3 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot4 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot5 WHEN '7' THEN 1 ELSE 0 END))/5 AS '1st Line'
FROM Data
INNER JOIN Employees ON Data.Name = Employees.ID
WHERE Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -1, getdate())), 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))
GROUP BY Employees.Name, Employees.Team
ORDER BY Employees.Team
This gives the result of 19.
The problem I have is that the query only returns a whole number that is rounded down. I need number to 2 decimal places.
I added a CAST to the query like this:
Select employees.Name, Employees.Team,
CAST((SUM(CASE Data.Slot1 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot2 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot3 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot4 WHEN '7' THEN 1 ELSE 0 END)+
SUM(CASE Data.Slot5 WHEN '7' THEN 1 ELSE 0 END))/5 AS Decimal(9,2)) AS '1st Line'
FROM Data
INNER JOIN Employees ON Data.Name = Employees.ID
WHERE Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -1, getdate())), 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))
GROUP BY Employees.Name, Employees.Team
ORDER BY Employees.Team
This gives the result of 19.00
This now returns a decimal but still rounds down to nearest whole number with two zeros after the decimal.
The result should be 19.20 (which is 96 divided by 5)
Could anyone tell me where I am going wrong please
Andy
January 6, 2010 at 4:15 am
You need to divide by 5.0 instead of 5.
Integer divided by integer will result in an integer again...
January 6, 2010 at 5:23 am
Many Many thanks Lutz.
That worked a treat
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply