How Can I do a group by with the result of a case expression-CodePudding

When I try to do a group by, using the result of a CASE expression named as Ticket, TOAD fails saying that Ticket it is not a valid item

SELECT 
CASE WHEN ( amt_1 >= 10000000                             ) THEN  'R100000'
     WHEN ( amt_1 <   9000000 and amt_1 >=  8000000       ) THEN  'R090000'
     WHEN ( amt_1 <   8000000 and amt_1 >=  7000000       ) THEN  'R080000'
     WHEN ( amt_1 <   7000000 and amt_1 >=  6000000       ) THEN  'R070000'
     WHEN ( amt_1 <   6000000 and amt_1 >=  5000000       ) THEN  'R060000'
     WHEN ( amt_1 <   5000000 and amt_1 >=  4000000       ) THEN  'R050000'
     WHEN ( amt_1 <   4000000 and amt_1 >=  3000000       ) THEN  'R040000'
     WHEN ( amt_1 <   3000000 and amt_1 >=  2000000       ) THEN  'R030000'
     WHEN ( amt_1 <   2000000 and amt_1 >=  1000000       ) THEN  'R020000'
     WHEN ( amt_1 <   1000000 and amt_1 >=  500000        ) THEN  'R010000'
     WHEN ( amt_1 <    500000 and amt_1 >=  100000        ) THEN  'R005000'
     WHEN ( amt_1 <    100000                             ) THEN  'R001000'             
END  as Ticket,    
CAST ( SUM(AMT_1/100) AS DECIMAL(10,2) )  
Count(*)     
FROM BASE24.PTLF 
GROUP BY  Ticket

CodePudding user response：

In Oracle SQL (and in the SQL Standard - although apparently not in all SQL dialects), you can't group by a column name (alias) that is only defined in the SELECT clause. The name Ticket is only assigned to your expression when the SELECT clause is executed, and that is after GROUP BY is processed - despite what the order of the clauses in the syntax may suggest.

The simplest solution is to write a subquery where you define Ticket as that case expression, then select from the subquery and use the GROUP BY clause as you have it.

Uglier: you can copy the entire case expression from the SELECT clause and paste it in the GROUP BY clause (replacing the column name with this entire expression). You may think this uglier query will be more efficient, because it doesn't require a subquery and an outer query; but that is not the case. The optimizer will eliminate the subquery from the "good looking" query (with a subquery) and it will rewrite the query as the "ugly" one all on its own, without your help.

Best: The standard should (but DOES NOT) allow you / us to write the case expression in the GROUP BY clause and give it an alias there, and then let us use the alias in the SELECT clause. Alas that is not in the standard, nor implemented in any dialect I know of, although logically there should be no problem with it: we are allowed to "create column names" in a lot of other places, like in pivot operations, in match_recognize, in the model clause, etc. Why not in group by - only the standard writers know.

CodePudding user response：

Use a common table expression:

WITH cteTicket_data
  AS (SELECT CASE
               WHEN ( amt_1 >= 10000000                      ) THEN 'R100000'
               WHEN ( amt_1 <   9000000 and amt_1 >=  8000000) THEN 'R090000'
               WHEN ( amt_1 <   8000000 and amt_1 >=  7000000) THEN 'R080000'
               WHEN ( amt_1 <   7000000 and amt_1 >=  6000000) THEN 'R070000'
               WHEN ( amt_1 <   6000000 and amt_1 >=  5000000) THEN 'R060000'
               WHEN ( amt_1 <   5000000 and amt_1 >=  4000000) THEN 'R050000'
               WHEN ( amt_1 <   4000000 and amt_1 >=  3000000) THEN 'R040000'
               WHEN ( amt_1 <   3000000 and amt_1 >=  2000000) THEN 'R030000'
               WHEN ( amt_1 <   2000000 and amt_1 >=  1000000) THEN 'R020000'
               WHEN ( amt_1 <   1000000 and amt_1 >=   500000) THEN 'R010000'
               WHEN ( amt_1 <    500000 and amt_1 >=   100000) THEN 'R005000'
               WHEN ( amt_1 <    100000                      ) THEN 'R001000'             
             END as Ticket,    
             CAST(AMT_1/100 AS DECIMAL(10,2) AS AMT
        FROM BASE24.PTLF)
SELECT TICKET,
       SUM(AMT),
       Count(*) AS TICKET_AMT_COUNT
  FROM cteTicket_data
  GROUP BY Ticket