Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2015
07:58 PM

02-16-2015
07:58 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2015
08:00 PM

02-16-2015
08:00 PM

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 .

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2015
09:12 PM

02-16-2015
09:12 PM

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

...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2015
10:04 PM

02-16-2015
10:04 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2015
10:36 PM

02-16-2015
10:36 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-17-2015
01:07 AM

02-17-2015
01:07 AM

Re: Adding count to a case when sum statement

Remove an existing GROUP BY?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-17-2015
06:43 AM

02-17-2015
06:43 AM

Re: Adding count to a case when sum statement

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-17-2015
08:41 PM

02-17-2015
08:41 PM

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"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-18-2015
03:00 AM

02-18-2015
03:00 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-19-2015
02:51 PM

02-19-2015
02:51 PM

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?