Oracle SQL Group by self made column?

Question

im having this Statement:

SELECT CASE
          WHEN teile LIKE 'S%' THEN
           'vent'
          ELSE
           teile
       END AS teile,
       SUM(amount) 
.
 GROUP BY bereich, teile

The result is something like this

D1    10070
ER    6278
MO    6278
vent  20140
vent  20140
vent  20140

Now i want to summarize the amount of every "vent" and group it another time to "vent" to look like this :

tried it several times with different things but i just cant get it..

score 4 · Answer 1 · answered Apr 19 '13 at 12:32

4

Another way in addition:

SELECT teile, SUM(amount) AS total_amt FROM
(
 SELECT (CASE WHEN teile LIKE 'S%' THEN 'vent' ELSE teile END) AS teile
       , amount 
FROM your_table
)
GROUP BY teile
/

answered Apr 19 '13 at 12:32

Art

5,616
1
20
22

+1: I prefer this method as it follows the "Don't Repeat Yourself" principle. – David Aldridge Apr 19 '13 at 12:38

score 1 · Accepted Answer · answered Apr 19 '13 at 11:58

1

Put the CASE expression from the SELECT clause in your GROUP BY clause:

SELECT CASE
          WHEN teile LIKE 'S%' THEN
           'vent'
          ELSE
           teile
       END AS teile,
       SUM(amount) 
...
 GROUP BY 
       CASE
          WHEN teile LIKE 'S%' THEN
           'vent'
          ELSE
           teile
       END;

answered Apr 19 '13 at 11:58

Vincent Malgrat

66,725
9
119
171

tried this before, but didnt work out - after looking in your statement more precisely it worked out - thx! – phips0r Apr 19 '13 at 12:07

Oracle SQL Group by self made column?

2 Answers2