Select from where as

Question

Using oracle, how can I do this using a single query?

VALUE       TYPE        IDentifier     
12           aa         123
123          bb         123
323          aa         1234
1212         cc         123
2            aa         12345
123          dd         123

SELECT IDentifier , SUM(VALUE) AS typeAASum WHERE TYPE = 'aa'
SELECT IDentifier , SUM(VALUE) AS typeBBSum WHERE TYPE = 'bb'
SELECT IDentifier , SUM(VALUE) AS typeCCSum WHERE TYPE = 'cc'

So my results will be, again in a single query,

   IDentifier     TypeAASum    TypeBBSum     TypeCCSum

It's important that I only get one row per Identifier.

score 2 · Accepted Answer · answered Oct 03 '14 at 20:02

2

SELECT Identifier,
        SUM(CASE WHEN TYPE = 'aa' THEN VALUE ELSE 0 END) AS typeAASum,
        SUM(CASE WHEN TYPE = 'bb' THEN VALUE ELSE 0 END) AS typeBBSum,
        SUM(CASE WHEN TYPE = 'cc' THEN VALUE ELSE 0 END) AS typeCCSum
FROM YourTable
GROUP BY Identifier

answered Oct 03 '14 at 20:02

Barmar

741,623
53
500
612

Is it really that simple...? – Patrick Schomburg Oct 03 '14 at 20:03
3

@PatrickSchomburg Yes, that's why he used that as an answer – Matt Busche Oct 03 '14 at 20:20
Worked perfect. The Value was a calculated field and still performed extremely well. – Patrick Schomburg Oct 03 '14 at 20:50
So just to get a better oracle grasp, does the CASE statements cause the query to check every row 3 times in this? – Patrick Schomburg Oct 03 '14 at 20:55

Select from where as

1 Answers1