Count with Case Select in Oracle

I have the following query:

SELECT t.range,
       Count(*)
FROM (
    SELECT CASE
            WHEN creditscore BETWEEN 300
                    AND 499
                THEN '[300, 499]'
            WHEN creditscore BETWEEN 500
                    AND 699
                THEN '[500, 699]'
            WHEN creditscore BETWEEN 700
                    AND 850
                THEN '[700, 850]'
            END AS range
    FROM customer
    ) T
GROUP BY t.range;

Which gives me the following :

CS range     COUNT(*)
---------- ----------
[700, 850]          7
[500, 699]         13

I want the following output:

CS range     COUNT(*)
---------- ----------
[300, 499]          0
[500, 699]         13
[700, 850]          7

Can anyone help me out?

UPDATE

I executed the code the Justin provided. It gives me the following:

DESCRIPTIO   COUNT(*)
---------- ----------
[300, 499]          1
[700, 850]          7
[500, 699]         13

SQL> SELECT COUNT(*) FROM customer where creditscore BETWEEN 300 AND 499;

COUNT(*)
----------
         0

One option would be to do something like

WITH ranges AS (
  SELECT '[300, 499]' description, 300 min_val, 499 max_val FROM dual
  UNION ALL
  SELECT '[500, 699]' description, 500 min_val, 699 max_val FROM dual
  UNION ALL
  SELECT '[700, 850]' description, 700 min_val, 850 max_val FROM dual
)
SELECT r.description, count(creditscore)
  FROM ranges r
       LEFT OUTER JOIN customer c
         ON (c.creditscore BETWEEN r.min_val AND r.max_val)
 GROUP BY r.description

Normally, you’d want to set up a permanent table with your ranges in case someone wants to add or modify them in the future. But you can hard-code the ranges in your query like I do here.

Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Count with Case Select in Oracle

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录