Need help to simplify this T-SQL query

Question

I have this working T-SQL query, is there any way to simplify it?

The SELECT COUNT(*) FROM Tablename is repeating and only the condition is different for each query, so is there a way to simplify this more? thank you

SELECT COUNT(*) FROM Table 
WHERE columnA = 'I' AND columnB = 'I' AND columnC =''

UNION ALL

SELECT COUNT(*) FROM Table 
WHERE columnA = 'I' AND columnB = 'I' AND columnC <>''

UNION ALL

SELECT COUNT(*) FROM Table
WHERE columnA IN ('A','R') AND columnB = 'I' AND columnD IN (SELECT columnE FROM Table2)

UNION ALL

SELECT COUNT(*) FROM Table 
WHERE columnA IN ('B') AND columnB = 'I'

UNION ALL

SELECT COUNT(*) FROM Table 
WHERE columnA IN ('R') AND columnB = 'S'

UNION ALL

SELECT COUNT(*) FROM Table 
WHERE columnA IN ('A') AND columnB = 'S'

This is the working query and my output is 6 rows, want to maintain that

score 0 · Answer 1 · answered Jul 08 '21 at 15:58

Let me assume that BI_BRH is unique in STDHOST. This is not strictly necessary but it are simplifying.

Then you can use conditional aggregation. I think putting the values in columns is the simplest approach:

SELECT SUM(CASE WHEN STATUS = 'I' AND TRX_STATE = 'I' AND ASSIGN_TO = '' THEN 1 ELSE 0 END),
       SUM(CASE WHEN STATUS = 'I' AND TRX_STATE = 'I' AND ASSIGN_TO <>'' THEN 1 ELSE 0 END)
       SUM(CASE WHEN STATUS IN ('A','R') AND TRX_STATE = 'I' AND s.BI_BRH IS NOT NULL THEN 1 ELSE 0 END)
       SUM(CASE WHEN STATUS IN ('B') AND TRX_STATE = 'I' THEN 1 ELSE 0 END)
       SUM(CASE WHEN STATUS IN ('R') AND TRX_STATE = 'S' THEN 1 ELSE 0 END)
       SUM(CASE WHEN STATUS IN ('A') AND TRX_STATE = 'S' THEN 1 ELSE 0 END)
FROM ICSCHQINFO i LEFT JOIN
     STDHOST s
     ON s.BI_BRH = i.BRH_ASSIGN_TO;

is there a way for it to be written in this format -> SELECT SUM........ FROM (SELECT........FROM......WHERE.....GROUP BY.........UNION ALL SELECT.......FROM........WHERE......GROUP BY.......)a GROUP BY........ — KelveenRaj, Jul 09 '21 at 09:27
@KelveenRaj . . . Perhaps. But there are simpler ways to unpivot if you want separate rows. And an explicit `GROUP BY` is not necessary. — Gordon Linoff, Jul 09 '21 at 12:57

forpas · Answer 2 · 2021-07-08T16:21:57.387

Your code runs 6 times over the same table, with different conditions in the WHERE clause and returns 6 rows.

Assuming that the 6 conditions you have are mutually exclusive, you can create a CTE that returns for each row the category that it belongs to and then aggregate on that category:

WITH cte AS (
  SELECT CASE 
           WHEN STATUS = 'I' AND TRX_STATE = 'I' AND ASSIGN_TO = '' THEN 'cat1'
           WHEN STATUS = 'I' AND TRX_STATE = 'I' AND ASSIGN_TO <> '' THEN 'cat2'
           WHEN STATUS IN ('A','R') AND TRX_STATE = 'I' AND BRH_ASSIGN_TO IN (SELECT BI_BRH FROM STDHOST) THEN 'cat3'
           WHEN STATUS IN ('B') AND TRX_STATE = 'I' THEN 'cat4'
           WHEN STATUS IN ('R') AND TRX_STATE = 'S' THEN 'cat5'
           WHEN STATUS IN ('A') AND TRX_STATE = 'S' THEN 'cat6'
         END category
  FROM ICSCHQINFO          
)
SELECT category, COUNT(*) counter
FROM cte
GROUP BY category

If there are no rows for any of the categories and in that case you want a row with 0, you need another CTE that returns the categories and LEFT join to the other CTE:

WITH 
  categories AS (SELECT category FROM (VALUES ('cat1'), ('cat2'), ('cat3'), ('cat4'), ('cat5'), ('cat6')) v(category)),
  cte AS (
    SELECT CASE 
             WHEN STATUS = 'I' AND TRX_STATE = 'I' AND ASSIGN_TO = '' THEN 'cat1'
             WHEN STATUS = 'I' AND TRX_STATE = 'I' AND ASSIGN_TO <> '' THEN 'cat2'
             WHEN STATUS IN ('A','R') AND TRX_STATE = 'I' AND BRH_ASSIGN_TO IN (SELECT BI_BRH FROM STDHOST) THEN 'cat3'
             WHEN STATUS IN ('B') AND TRX_STATE = 'I' THEN 'cat4'
             WHEN STATUS IN ('R') AND TRX_STATE = 'S' THEN 'cat5'
             WHEN STATUS IN ('A') AND TRX_STATE = 'S' THEN 'cat6'
           END category
    FROM ICSCHQINFO          
  )
SELECT c.category, COUNT(t.category) counter
FROM categories c LEFT JOIN cte t
ON t.category = c.category
GROUP BY c.category

Is there a way for it to be written in this format -> SELECT SUM........ FROM (SELECT........FROM......WHERE.....GROUP BY.........UNION ALL SELECT.......FROM........WHERE......GROUP BY.......)a GROUP BY........ — KelveenRaj, Jul 09 '21 at 09:28
@KelveenRaj Why? You already have the code with UNION ALL. Don't you want to simplify it? — forpas, Jul 09 '21 at 09:30

Need help to simplify this T-SQL query

2 Answers2