SQL: How can I make sure that all SELECT elements are represented, even if not all are complete?

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 problem is that I want all cases to display a row in my result set, but since I don't have values ​​that fall between 1 and 4999, this row does not appear. Anyway, I would like this row to be displayed and just contain 0 columns for it (except for the first, of course). Can anyone show me how to change the code to execute this? Maybe I need to do it differently ... Thanks!

Example result set I'm looking for ...

  | [MONEY] | [#] | [MAX] | | $ 20,000+ | 2 | 30,000 | | $ 10,000 - $ 19,999 | 8 | 19,000 | | $ 5,000 - $ 9,999 | 4 | 8,000 | | $ 1 - $ 4,999 | 0 | 0 | <-- Row currently doesn't show | $ 0 | 12 | 0 | 

Answer 1

You can create a lookup table using CTE, and then use it to group instead of the Case statement. You will need to make three more changes.

• COUNT must be COUNT (t.Money), or you will get 1 when you expect ZERO.
• You may want to COALESCE your MAX (Money), but I'm not sure what you want when its NULL
• In fact, you cannot order MAX (MONEY) because it can be zero. Therefore, it is better to use CTE for order management, as well as

 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

Answer 2

You can save descriptive text in a real or ephemeral table and accordingly join something like:

 ;with ranges(min,max,caption) as ( select 10000, 19999, '$ 10,000 - $19,999' union select 5000, 9999, '$ 5,000 - $ 9,999' union select 1, 4999, '$ 1 - $ 4,999' ) select isnull(r.caption, 'no description'), count(m.money) as [#], isnull(max(m.money), 0) from mytable m full outer join ranges r on m.money between r.min and r.max group by r.caption 

Answer 3

You can add values ​​and then insert an additional group (there is no access to SSMS for testing):

 SELECT [MONEY], SUM([#]) AS [#], MAX([MAX]) AS [MAX] FROM (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 UNION ALL SELECT '$ 0' AS [MONEY], 0 AS [#], 0 AS [MAX] UNION ALL SELECT '$ 1 - $ 4,999' AS [MONEY], 0 AS [#], 0 AS [MAX] UNION ALL SELECT '$ 5,000 - $ 9,999' AS [MONEY], 0 AS [#], 0 AS [MAX] UNION ALL SELECT '$ 10,000 - $19,999' AS [MONEY], 0 AS [#], 0 AS [MAX] UNION ALL SELECT '$ 20,000 + ' AS [MONEY], 0 AS [#], 0 AS [MAX] ) SUB GROUP BY [MONEY] 

Answer 4

Hmmm ... what would I do:

Instead of table t

I would choose from a subquery

 From ( Select Money, 1 as ForReal from Table t Union Select 1, 0 Union Select 5000, 0 Union Select 10000, 0 Union Select 20001, 0 ) 

And my best bet would be Max (Money * ForReal), so you get your group and 0 as MaxAmount ..