Query Help

Question

Post reply

Query Help

mbrady5

SSCrazy

Points: 2835
More actions
October 8, 2009 at 12:03 pm

#210754

I would like to count the 'status' and divide that count into (the count of where status = 'won'), so I can calculate my win percentage. Would someone be kind enough to help me with this? Beyond my skill lebel....
SELECT [CWS Division], Zone, Status, COUNT(*) AS Count, [CWS Division] AS Count
FROM New_Call
WHERE ([Sales Rep] IN (@salesrep)) AND (Status IN (@status)) AND ([CWS Division] IN (@division)) AND ([Date Quoted] >= @start_date) AND
([Date Quoted] < DATEADD(Day, DATEDIFF(Day, 0, @end_date) + 1, 0))
GROUP BY Zone, Status, [CWS Division]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 1

Try this...the two additional columns I added are set apart with a carriage return before and after.

SELECT [CWS Division],

[Zone],

[Status],

COUNT(1) AS Count,

SUM(CASE WHEN [Status] = 'Won' THEN 1 ELSE 0 END) AS NumberOfWins,

SUM(CASE WHEN [Status] = 'Won' THEN 1 ELSE 0 END) / COUNT(1) AS WinPct,

[CWS Division] AS Count

<snip>

MJM

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 2

Mark Marinovic (10/8/2009)
Try this...the two additional columns I added are set apart with a carriage return before and after.
SELECT [CWS Division],
[Zone],
[Status],
COUNT(1) AS Count,
SUM(CASE WHEN [Status] = 'Won' THEN 1 ELSE 0 END) AS NumberOfWins,
SUM(CASE WHEN [Status] = 'Won' THEN 1 ELSE 0 END) / COUNT(1) AS WinPct,
[CWS Division] AS Count
<snip>
MJM

The WinPct looks like it will end up being only integer math which will likely return only 0 or 1 as a result of the division. One small tweak may remedy that.

SELECT [CWS Division],

[Zone],

[Status],

COUNT(1) AS Count,

SUM(CASE WHEN [Status] = 'Won' THEN 1 ELSE 0 END) AS NumberOfWins,

SUM(CASE WHEN [Status] = 'Won' THEN 1.0 ELSE 0.0 END) / COUNT(1) AS WinPct,

[CWS Division] AS Count

<snip>

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 3

Thanks, Jeff - good catch. Depending on your specific requirements, you can also look into using the CAST function on the dividend to get the specificity needed (e.g. NUMERIC(19,5), etc.)

MJM

mbrady5 SSCrazy Points: 2835 More actions · Answer 4

Ok ...I am getting there..Made a couple of changes.

What I am actually trying to do is divide the number of wins by the total count of status. For some reson this is not calculating the proper win %. Count(1) seems should be summing the total amount of calls..

SELECT [CWS Division], Zone,

COUNT(1) AS Count, SUM(CASE WHEN [Status] = 'Won' THEN 1 ELSE 0 END) AS NumberOfWins,

SUM(CASE WHEN [Status] = 'Won' THEN 1 ELSE 0 END) / COUNT(1) AS WinPct

FROM New_Call

WHERE (Zone IS NOT NULL) AND ([CWS Division] IS NOT NULL)

GROUP BY Zone, [CWS Division]

ORDER BY [CWS Division]

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 5

mbrady, I am a visual learner 😛

Could you post a quick CREATE/INSERT script with some dummy data and the results you expect to see? Most likely the issue is with the GROUPING level. I *think* you want the sum total of the whole population but my calc is just going to give you a percentage of wins over that particular grouping count.

Thanks!

MJM