0

I've got two mySQL tables, Table A and B. I need to get an output like in Table 3.

Below mentioned is the code I tried with Full Join and does not give me the intended result. Much appreciate your help..

SELECT DISTINCT(Table_A.Code) as 'Code', SUM(Table_A.Qty_On_Hand) as 'On Hand Qty', SUM(Table_B.Counted_Qty) as 'Counted Qty'
FULL JOIN Table_B ON Table_A.Code = Table_B.Code
FROM Table_A

Table A

Code On Hand Qty
A 20
B 10
B 20
B 50
C 60

Table B

Code Counted Qty
A 10
B 0
C 30
B 0
C 10

Out put required:

Code On Hand Qty Counted Qty
A 20 10
B 80 0
C 60 40
Mohan Wijesena
  • 225
  • 1
  • 3
  • 11
  • `DISTINCT` isn't a function. It's a keyword that makes the entire select list unique, it doesn't apply to a specific column. – Barmar Feb 18 '21 at 02:05
  • That required output seems to be incorrect. Shouldn't the `Counted Qty` column for `C` return `40`? – FanoFN Feb 18 '21 at 02:08

1 Answers1

0

You need to use GROUP BY Table_A.Code, not DISTINCT.

SELECT a.Code, SUM(a.Qty_On_Hand) AS `On Hand Qty`, b.`Counted Qty`
FROM Table_A as a
JOIN (
    SELECT Code, SUM(Counted_Qty) AS `Counted Qty`
    FROM Table_B
    GROUP BY Code
) AS b ON a.Code = b.Code
GROUP BY a.Code

You need to do one of the SUMs in a subquery, otherwise you'll multiply its sums by the number of rows in the other table. See Join tables with SUM issue in MYSQL.

Barmar
  • 741,623
  • 53
  • 500
  • 612