Analytics

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

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

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

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

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

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

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

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

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

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?

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.