Adding count to a case when sum statement

Analytics
N/A

Adding count to a case when sum statement

I have the following CASE WHEN statement in my query and would like to get a count of each "category", so output would look something like this:

<0.5   0.5-1.0   1.001-2.0   2.001-3.0   3.001-4.0   >4.001
12 23 56 75 34 27

select

(CASE WHEN SUM (d.DCG_WGTD_RETROSPCTV_RISK_NBR) <'0.5' THEN '<0.5' (WOULD LIKE A COUNT OF HOW MANY RECORDS FALL INTO EACH OF THESE)

WHEN SUM (d.DCG_WGTD_RETROSPCTV_RISK_NBR) between '0.5' and '1.0' THEN '0.5-1.0'

WHEN SUM (d.DCG_WGTD_RETROSPCTV_RISK_NBR) between '1.001' and '2.0' THEN '1.1-2.0'

WHEN SUM (d.DCG_WGTD_RETROSPCTV_RISK_NBR) between '2.001' and '3.0' THEN '2.1-3.0'

WHEN SUM (d.DCG_WGTD_RETROSPCTV_RISK_NBR) between '3.001' and '4.0' THEN '3.1-4.0'

WHEN SUM (d.DCG_WGTD_RETROSPCTV_RISK_NBR) >='4.01' THEN '>4.1'

END AS DXCG_RNGE)

Any help you can provide is greatly appreciated.

10 REPLIES
N/A

Re: Adding count to a case when sum statement

For some reason my sample output above posted vertically when I pasted it as a horizontil table, not sure why it did that. The output should horizontal with column names being <0.5, 1.0-2.0, etc with the count under each .

N/A

Re: Adding count to a case when sum statement

You need a seperate CASE for every column:

select

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR) < 0.5 THEN 1 ELSE 0 END) AS "<0.5",

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR) between 0.5 and 1.0 THEN 1 ELSE 0 END) AS "0.5-1.0",

...
N/A

Re: Adding count to a case when sum statement

Thank you Dieter for the reply but now I am getting error 3707 , look like it wants something between _risk_nbr and )??  It does seem I am missing a closing ) but I've tried placing this in different places and still no luck. Can you think of something else to try??

N/A

Re: Adding count to a case when sum statement

My apologies, I got the query to run, but the output is not exactly how I would like...

this is the output below; however I would like all the values totaled as one number, so only 1 row under the column heading with a total count.




<0.5
1
3
0
0
0
0
0

I imagine I need to add some type of count statement onto these??

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR < '0.5' THEN 1 ELSE 0 END)AS "<0.5",

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR between '0.5' and '1.0' THEN 1 ELSE 0 END) AS "0.5-1.0",

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR between 1.001 and 1.5 THEN 1 ELSE 0 END) AS "1.0-1.5",

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR between 1.5001 and 2.0 THEN 1 ELSE 0 END) AS "1.5-2.0",

N/A

Re: Adding count to a case when sum statement

Remove an existing GROUP BY?

N/A

Re: Adding count to a case when sum statement

Dieter - that did the trick! Thank you very much, your help is GREATLY appreciated.

N/A

Re: Adding count to a case when sum statement

Can I ask someone to check my syntax for this query as well. I have been able to get it to run successfully but I am questioning whether I'm capturing all possible ranges the way its written. Values are not whole numbers and can be several digits. I'm worried there may be gaps in the ranges? do I need to include an = in there as well?

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR < '0.50' THEN 1 ELSE 0 END)AS "<0.5",

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR between '0.50' and '1.0' THEN 1 ELSE 0 END) AS "0.5-1.0",

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR between 1.0 and 2.0 THEN 1 ELSE 0 END) AS "1.0-2.0",

SUM (CASE WHEN d.DCG_WGTD_RETROSPCTV_RISK_NBR > '2.0' THEN 1 ELSE 0 END)AS ">2.0"

N/A

Re: Adding count to a case when sum statement

BETWEEN includes both start and end values, so 

 "between '0.50' and '1.0'" and "between 1.0 and 2.0" overlap at 1.0

And you shouldn't use quoted strings for numeric values, 1.0 instead of '1.0'.

N/A

Re: Adding count to a case when sum statement

SUM (CASE WHEN d.DCG < 0.50 THEN 1 ELSE 0 END)AS "<0.5",

SUM (CASE WHEN d.DCG between >=0.50 and <1.0 THEN 1 ELSE 0 END) AS "0.5-1.0",

SUM (CASE WHEN d.DCG between >=1.0 and <2.0 THEN 1 ELSE 0 END) AS "1.0-2.0",

SUM (CASE WHEN d.DCG >=2.0 THEN 1 ELSE 0 END)AS ">2.0"

Tried this and still getting an error: 3706 expected something between the "between" keyword and ">=".

Works fine when I remove the >= but then I'm double counting some values. Any idea whats wrong here?