All Forums Analytics
Mooli 21 posts Joined 12/14
16 Feb 2015
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.
 

Mooli 21 posts Joined 12/14
16 Feb 2015

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 .

dnoeth 4628 posts Joined 11/04
16 Feb 2015

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",

...

 

Dieter

Mooli 21 posts Joined 12/14
16 Feb 2015

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??
 
 

Mooli 21 posts Joined 12/14
16 Feb 2015

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",

dnoeth 4628 posts Joined 11/04
17 Feb 2015

Remove an existing GROUP BY?

Dieter

Mooli 21 posts Joined 12/14
17 Feb 2015

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

Mooli 21 posts Joined 12/14
17 Feb 2015

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"

dnoeth 4628 posts Joined 11/04
18 Feb 2015

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'.

Dieter

Mooli 21 posts Joined 12/14
19 Feb 2015

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?
 

RonaqTaneja 2 posts Joined 02/15
24 Feb 2015

Hi,
I hope it'll work fine when you remove "between" keyword from your query.
 

You must sign in to leave a comment.