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 .

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

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

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

Remove an existing GROUP BY?

Dieter

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

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"

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

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?

Hi,

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

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:

Any help you can provide is greatly appreciated.