SQL: How can I make sure all SELECT Cases are represented even if not all are executed?

Question

Here is my code ``` SELECT CASE WHEN Money >= 20000 THEN '$ 20,000 + ' WHEN Money BETWEEN 10000 AND 19999 THEN '$ 10,000 - $ 19,999' WHEN Money BETWEEN 5000 AND 9999 THEN '$ 5,000 - $ 9,999' WHEN Money BETWEEN 1 AND 4999 THEN '$ 1 - $ 4,999' ELSE '$ 0' END AS [MONEY], COUNT(*) AS [#], MAX(Money) AS [MAX] FROM MyTable WHERE MoneyType = 'Type A' GROUP BY CASE WHEN Money >= 20000 THEN '$ 20,000 + ' WHEN Money BETWEEN 10000 AND 19999 THEN '$ 10,000 - $ 19,999' WHEN Money BETWEEN 5000 AND 9999 THEN '$ 5,000 - $ 9,999' WHEN Money BETWEEN 1 AND 4999 THEN '$ 1 - $ 4,999' ELSE '$ 0' END ORDER BY MAX DESC ``` Now my issue is I want all the cases to show a row in my result set but, since I dont have any values that would fall between 1 AND 4999 that row doesn't show up. I would still like that row to show up and just contain 0's for it's columns (except the first of course). Anyone can show me how to modify the code to accomplish this? Perhaps I need to do it a different way... Thanks! Example o…

Accepted Answer

You could build up a lookup table with a CTE and then use that to group on that instead of the Case statement. You'll need to make three other changes

- The COUNT will need to be COUNT(t.Money) or you'll get a 1 when you're expecting a ZERO.

- You may want to COALESCE your MAX(Money) but I'm not sure what you want it to be when its NULL

- You can't actually order by MAX(MONEY) because it may be null. So its better to use the CTE to control the order as well

```
WITH Ranges AS
( SELECT 1 id , '$ 20,000 +'  description
  UNION SELECT 2 , '$ 10,000 - $19,999'
  UNION SELECT 3, '$  5,000 - $ 9,999'
  UNION SELECT 4, '$      1 - $ 4,999'
  UNION SELECT 5, '$      0')

SELECT
       r.Description as money,
       COUNT(t.Money) AS [#],
       MAX(Money) AS [MAX]
  FROM 
       Ranges r
       LEFT JOIN
        MyTable t
       ON r.ID =   CASE
                    WHEN Money >= 20000 THEN 1
                    WHEN Money BETWEEN 10000 AND 19999 THEN 2
                    WHEN Money BETWEEN  5000 AND  9999 THEN 3
                    WHEN Money BETWEEN     1 AND  4999 THEN 4
                    ELSE 5
                 END 
        AND  MoneyType = 'Type A' 
 GROUP BY 
  r.id,
  r.Description
 ORDER BY r.id asc
```

LIVE DEMO

SQL: How can I make sure all SELECT Cases are represented even if not all are executed?

Solution

Problem