CODEWITHSUNDEEP
sqlsum
vaka
vaka

Reputation: 61

Sum of sums in different tables

I have problem with SQL query.
I have tableA:

productA priceA  
P1        18  
P2        35  
P1        22  
P2        19

and also tableB:

productB priceB  
P1        3  
P2        15  
P1        80  
P2        96

I want as result the sum of two products from the 2 tables.

product price  
P1       123  
P2       165

I want to sum the sums of the two tables.
I am trying this query but it's wrong.

SELECT productA, 
     (SELECT SUM(priceA) FROM tableA GROUP BY productA), 
     (SELECT SUM(priceB) FROM tableB GROUP BY productB)
FROM tableA, tableB
WHERE productA = productB
GROUP BY productA

Please help me.

Upvotes: 6

Views: 931

Answers (3)

Bohemian
Bohemian

Reputation: 425033

This is quite literally the sum of sums:

select
    product,
    sum(price)
from (
    select productA as product, sum(priceA) as price from tableA group by 1
    union all
    select productB, sum(priceB) from tableB group by 1
) 
group by 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Because a join seems like a natural way to approach this problem, here is a solution with join rather than union. It aggregates the data in subqueries first, then joins the results together:

select coalesce(a.productA, b.productB) as product,
       coalesce(a.PriceA, 0) + coalesce(b.PriceB, 0) as price
from (select productA, sum(PriceA) as PriceA
      from TableA
      group by productA
     ) a full outer join
     (select productB, sum(PriceB) as PriceB
      from TableB
      group by productB
     ) b
     on a.productA = b.prodctB

I'm using a full outer join in case the tables have different sets of products. As a result, I need to have the coalesce in the select statement.

Upvotes: 0

Andomar
Andomar

Reputation: 238086

You could use a union to merge the tables, and group by on the result:

select  product
,       sum(price)
from    (
        select  productA as product
        ,       priceA as price
        from    TableA
        union all
        select  productB
        ,       priceB
        from    TableB
        ) as SubQueryAlias
group by
        product

Upvotes: 3

Related Questions