Yasser A Yasser A - 1 month ago 6
SQL Question

Union columns in same table and show sum of two columns based on the ID

I'm trying to find a query that can help me doing the following, I have one table like bellow

ID1 VALUE1 ID2 VALUE2
=========================
1 1 2 2
1 2 1 2
2 3 2 4
2 4 3 5
3 6 1 5
4 2 1 1


I'm looking for

ID VALUE1 VALUE2
=========================
1 3 8
2 7 6
3 6 5
4 2 0


Basically, search in ID1 and shows sum in Value1 and search same ID in ID2 and shows sum of Value2

I tried

SELECT
ID1 as ID,
SUM(VALUE1) AS VALUE1,
SUM(VALUE2) AS VALUE2
FROM TABLE
GROUP BY ID1;


but the query above does not show what I want.

I made this where it union both IDs

SELECT ID1 as ID from TABLE
UNION
SELECT ID2 from TABLE


but then I do't know how to use the created columns to search on Value1 and Value2

Answer

Use union all to combine the four columns into a more reasonable format. Then aggregate with group by:

select id, sum(value1), sum(value2)
from ((select id1 as id, value1 as val1, 0 as val2 from t
      ) union all
      (select id2, 0, value2 from t
      )
     ) tt
group by id;
Comments