CODEWITHSUNDEEP
sqlselectamazon-redshiftunion
Zhezhong Jiang
Zhezhong Jiang

Reputation: 83

SQL aggregate data from multiple tables and sum a specified column

I have three tables as below:
Table 1 : 

user_id   user_label   code1   count1 
-------   ----------   ------  ------
1         x            a       1 
1         x            c       1
1         y            a       1 
2         x            a       1


Table 2 : 

user_id   user_label   code2   count2 
-------   ----------   ------  ------
1         x            b       1 
1         x            d       2
1         y            b       1 
2         x            b       1


Table 3 : 

user_id   user_label   code3   count3 
-------   ----------   ------  ------
1         x            c       1 
1         x            e       1
1         y            c       1 
2         x            c       1

And I would like to sum the count from these three tables for the same user_id + user_label + code , and keep the rest records, the desired result would look like below :

user_id   user_label   code   total_count 
-------   ----------   ------  ------
1         x            a       1 
1         x            c       2
1         x            b       1 
2         x            d       2
1         x            e       1
1         y            a       1
1         y            b       1
1         y            c       1
2         x            a       1
2         x            b       1
2         x            c       1

The records (1,x,c) can be found both in Table 1 and Table 3 and thus their count should be summed, and the rest stays the same in the result table.
Right now what I have in mind is using UNION operation like below :

SELECT * FROM tb1   UNION
SELECT * FROM tb2  UNION
SELECT * FROM tb3

This will give me all distinct rows from those three tables but I am not sure how to do the summation on counts on top of that, any help or suggestion would be appreciated.

Upvotes: 2

Views: 864

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You don't have duplicates within tables, so you can also use full join:

select user_id, user_label, code1,
       (coalesce(t1.count1, 0) + coalesce(t2.count1, 0) + coalesce(t3.count1, 0)
       ) as total_count
from table1 t1 full join
     table2 t2
     using (user_id, user_label, code1) full join
     table3 t3
     using (user_id, user_label, code1) ;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311028

As you noted, union will remove duplicates, so you should use union all. Once you do that, you can wrap that query with an aggregate query to get the sum of the counts:

SELECT   user_id, user_label, code, SUM(cnt) AS total_count
FROM     (SELECT user_id, user_label, code1 as code, count1
          FROM   table1
          UNION ALL
          SELECT user_id, user_label, code2, count2
          FROM   table2
          UNION ALL
          SELECT user_id, user_label, code3, count3
          FROM   table3) t
GROUP BY user_id, user_label, code

Upvotes: 2

MichaelD
MichaelD

Reputation: 1326

I would be more explicit in the select statements then wrap the union into a subquery.

SELECT user_id, user_label,code, SUM(x_count) as total_count FROM
    SELECT user_id, user_label, code1 as code, count1 as x_count
    FROM tb1 
    UNION
    SELECT user_id, user_label, code2 as code, count2 as x_count
    FROM tb2 
    UNION
    SELECT user_id, user_label, code3 as code, count3 as x_count
    FROM tb3)
GROUP BY user_id, user_label, code

Upvotes: 1

Related Questions