How to aggregate on the same column?

Question

I am trying to figure out which seems very simple but I am not able to figure out a way to do same.

Consider following table and data,

create table dummy_query 
(id varchar2(20), amount number(10,2), memo varchar(20));

insert into dummy_query values('1', 10.00, 'Memo');

insert into dummy_query values('1', 20.00, 'Memo1');

I want to get the values as:

Id MemoValue  Memo1Value TotalSum

----------

1   10.00       20.00       30.00

Is there any way to get data in this manner?

Thanks!

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table dummy_query (id, amount, memo) AS
           SELECT '1', 10.00, 'Memo'  FROM DUAL
UNION ALL  SELECT '1', 20.00, 'Memo1' FROM DUAL
UNION ALL  SELECT '1', 30.00, 'Memo2' FROM DUAL;

Query 1:

If the TotalSum is the total of all memo amounts (and not just Memo and Memo1) then you can do:

SELECT ID,
       SUM( CASE memo WHEN 'Memo'  THEN amount END ) AS MemoValue,
       SUM( CASE memo WHEN 'Memo1' THEN amount END ) AS Memo1Value,
       SUM( amount ) AS TotalSum
FROM   dummy_query
GROUP BY id

Results:

| ID | MEMOVALUE | MEMO1VALUE | TOTALSUM |
|----|-----------|------------|----------|
|  1 |        10 |         20 |       60 |

Query 2:

But if the TotalSum is just MemoValue + Memo1Value then add in a where clause:

SELECT ID,
       SUM( CASE memo WHEN 'Memo'  THEN amount END ) AS MemoValue,
       SUM( CASE memo WHEN 'Memo1' THEN amount END ) AS Memo1Value,
       SUM( amount ) AS TotalSum
FROM   dummy_query
WHERE  memo IN ( 'Memo', 'Memo1' )
GROUP BY id

Results:

| ID | MEMOVALUE | MEMO1VALUE | TOTALSUM |
|----|-----------|------------|----------|
|  1 |        10 |         20 |       30 |

Query 3:

Or, if you need to include all rows for another reason, then you could do:

SELECT ID,
       SUM( CASE memo WHEN 'Memo'  THEN amount END ) AS MemoValue,
       SUM( CASE memo WHEN 'Memo1' THEN amount END ) AS Memo1Value,
       SUM( CASE WHEN memo IN ( 'Memo', 'Memo1' ) THEN amount END ) AS TotalSum
FROM   dummy_query
GROUP BY id

Results:

| ID | MEMOVALUE | MEMO1VALUE | TOTALSUM |
|----|-----------|------------|----------|
|  1 |        10 |         20 |       30 |

Answer 2

Solution with pivot, which you already started:

select id, memo, memo1, memo+memo1 msum from ( 
    select * from dummy_query 
    pivot (sum(amount) for (memo) in ('Memo' memo,'Memo1' memo1)))

and more traditional, without pivot:

select id, memo, memo1, memo1+memo msum from (
  select id, 
    sum(case when memo='Memo' then amount end) memo, 
    sum(case when memo='Memo1' then amount end) memo1
  from dummy_query group by id)

^SQLFiddle

Answer 3

Im not very familiar with PIVOT but what you have tried I did build query on that. Hope this is out put you need -

SELECT a.*, b.SUM_AMT
  FROM ((SELECT ID, amount, memo FROM dummy_query) PIVOT (SUM (amount)
                                                   FOR (memo)
                                                   IN ('Memo', 'Memo1'))) a,
       (  SELECT ID, SUM (AMOUNT) "SUM_AMT"
            FROM dummy_query
        GROUP BY id) b
 WHERE a.id = b.id

There can be better ways to reslove this so lets see what other folks have to share.