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

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

CodePudding user response：

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.

CodePudding user response：

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;