how to find total sum of the same products from two different tables?-CodePudding

Got 2 tables - baskets of products: basket_a

 a | fruit_a  | number_a 
--- ---------- ----------
 3 | Banana   |        0
 4 | Cucumber |        0
 1 | Apple    |       50
 2 | Orange   |       45

basket_b

 b |  fruit_b   | number_b 
--- ------------ ----------
 3 | Watermelon |        0
 4 | Pear       |        0
 1 | Orange     |        5
 2 | Apple      |       30

I need to find total amount of the fruits which match in both tables:

 fruit   | number 
--- ----------------
 Orange  |    80
 Apple   |    55

I tried inner join

select a.fruit_a, a.number_a, b.fruit_b, b.number_b from basket_a as a inner join basket_b as b on a.fruit_a=b.fruit_b;

 fruit_a | number_a | fruit_b | number_b 
--------- ---------- --------- ----------
 Apple   |       50 | Apple   |       30
 Orange  |       45 | Orange  |        5

and i tried union: select * from basket_a union select * from basket_b;

 a |  fruit_a   | number_a 
--- ------------ ----------
 1 | Orange     |        5
 2 | Apple      |       30
 4 | Pear       |        0
 3 | Watermelon |        0
 4 | Cucumber   |        0
 2 | Orange     |       45
 1 | Apple      |       50
 3 | Banana     |        0

But I couldn't group it somehow

select foo.fruit_a, foo.number_a from (select * from basket_a union select * from basket_b) as foo group by foo.fruit_a;

ERROR:  column "foo.number_a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select foo.fruit_a, foo.number_a from (select * from basket_...

CodePudding user response：

You where doing something like:

SELECT a.*, b.*
FROM basket_a
INNER JOIN basket_b ON basket_a.fruit_a = basketb_fruitb;

To get 2, and not 4 columns do:

SELECT 
    basket_a.fruit_a, 
    basket_a.number_a   basket_b.number_b as number
FROM basket_a
INNER JOIN basket_b ON basket_a.fruit_a = basketb_fruitb;

Another way, without inner join, is:

SELECT
   fruit_a,
   sum(number_a)
FROM (
      SELECT fruit_a, number_a FROM basket_a
      UNION ALL
      SELECT fruit_b, number_b FROM basket_b 
     )x
GROUP BY fruit_a;

CodePudding user response：

select fruit,sum(number) total
from
(
    select fruit_a,number
    from basket_a
    union all
    select fruit_b,number
    from basket_b
) t
group by fruit