November 3, 2009 at 1:07 pm
I have the followinf query which gives me the results I want, now I want to divide them to give me a percentage.
SELECT Count(CallLog.ClosedBy) AS Tracker From CallLog WHERE ClosedDate Between '2009-09-01' and '2009-10-30'and Tracker = 'Troy'
Union ALL
Select Count(CallLog.RecvdBy) AS Tracker From CallLog WHERE RecvdDate Between '2009-09-01' and '2009-10-30' and Tracker = 'Troy'
Any help is greatly appreciated.
November 3, 2009 at 1:19 pm
Do you mean like this:
select (SELECT Count(CallLog.ClosedBy) AS Tracker From CallLog WHERE ClosedDate Between '2009-09-01' and '2009-10-30'and Tracker = 'Troy'(
/
(Select Count(CallLog.RecvdBy) AS Tracker From CallLog WHERE RecvdDate Between '2009-09-01' and '2009-10-30' and Tracker = 'Troy')
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 1:20 pm
That gives me a
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
So I changed to )
and I get 0 but that is not the right value
November 3, 2009 at 1:22 pm
Multiply the counts inside the sub-queries by 1.0.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 1:25 pm
select (SELECT Count(CallLog.ClosedBy)*1 AS Tracker From CallLog WHERE ClosedDate Between '2009-09-01' and '2009-10-30'and Tracker = 'Troy')
/
(Select Count(CallLog.RecvdBy)*1 AS Tracker From CallLog WHERE RecvdDate Between '2009-09-01' and '2009-10-30' and Tracker = 'Troy')
Gives 0
November 3, 2009 at 2:50 pm
No, no, no. GSquared said to multiply by 1.0 not by 1 as you did. There is a difference.
Example: select 1 / 2 returns 0 where as select (1 * 1.0) / (2 * 1.0) returns 0.5
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply