Calculating the Difference Between Two Values in two tables using group by in mysql

Question

I have a table as shown below. I want to do a partition and then subtract the values in two tables to get the difference using group by.

Table 1:

id	type	name	amount
1	sale	sam	2
2	sale	sam	15
3	sale	lilly	25
4	sale	anju	20

Table 2:

id	type	name	amount
1	return	lilly	20
2	return	sam	3
2	return	xyz	7

And Need to return:

name	amount
sam	14
lilly	5
anju	20
xyz	-7

Answer 1

Aggregate sales and returns first, join them via user and subtract one from the other. COALESCE is used so that when no return records are found, you get a 0 instead:

SELECT sold.name
     , sold.amount AS sold_amount
     , COALESCE(returned.amount, 0) AS returned_amount
     , sold.amount - COALESCE(returned.amount, 0) AS final_sold_amount
  FROM (SELECT name, SUM(amount) AS amount
          FROM sales
         GROUP BY name
     ) sold -- sales aggregated by name
  LEFT OUTER
  JOIN (SELECT name, SUM(amount) AS amount
          FROM returns
         GROUP BY name
     ) returned -- returns aggregated by name
    ON sold.name = returned.name

Working demo on dbfiddle

PS. You don't actually need both aggregates to happen in subqueries - you could for example aggregate the sales in the main query and only the returns in a subquery. I used subqueries for both for easier understanding.

Answer 2

It can be done using UNION ALL operation and GROUP BY clause.

SELECT NAME,SUM(SALES-RETURN) AS AMOUNT
FROM
(
SELECT NAME,AMOUNT AS SALES,0 AS RETURN
FROM TABLE1
UNION ALL
SELECT NAME,0 AS SALES,AMOUNT AS RETURN
FROM TABL2
) REPORT
GROUP BY NAME
ORDER BY NAME;