CODEWITHSUNDEEP
sqlsql-server
Display_name
Display_name

Reputation: 103

SQL: Subtracting SUMs from 2 different tables

I am trying to find the amount of points each member has. The two relevant tables are LESSON and POINT. I must first sum up the points bought, POINT.Point, and then subtract the sum points used, LESSON.LessonPoint. Both operations must naturally use GROUP BY MemberId.

I initially tried the following query, but it was calculating the SUM for every record in the LESSON table. For example, if the real sum of points bought were equal to 10 and five lessons of 1 point each were taken, the result would come out to 45. The sum, 10, minus 1 point 5 times ((10-1)*5=45). However, the result sought after is 5 (10 points bought minus 5 points used). PIA = Point In Account.

SELECT P1.MemberId, SUM(P1.Point)-SUM(L1.LessonPoint) AS PIA
FROM POINT P1 LEFT JOIN LESSON L1 ON P1.MemberId= L1.MemberID GROUP BY P1.MemberId
ORDER BY P1.MemberId DESC

I only want to calculate each SUM once, so I need some kind of subquery. I then tried the following, but I got an error saying it didn't recognize SLP. SLP = Sum of Lesson Points.

SELECT P1.MemberId, SUM(P1.Point)-SLP AS PIA FROM POINT P1 FULL JOIN LESSON L1 ON P1.MemberId=L1.MemberId GROUP BY P1.MemberId WHERE P1.MemberId IN (SELECT SUM(L1.LessonPoint) AS SLP FROM LESSON L1 GROUP BY L1.MemberId)

Then I tried breaking down the problem. However, the following gave results similar to the first query in that the SUM of POINT.Point was calculated for every record in LESSON.LessonPoint corresponding to the MemberId.

SELECT P1.MemberId, SUM(P1.Point), SUM(L1.LessonPoint) FROM POINT P1 FULL JOIN LESSON L1 ON L1.MemberId=P1.MemberId GROUP BY L1.MemberId, P1.MemberId

I hope this question was clearly formulated. I have reached the limit of my knowledge and abilities, please help.

Upvotes: 0

Views: 707

Answers (2)

mohan111
mohan111

Reputation: 8865

;WITH  CTE (MemberId,p1sum,l1sum) AS 
(
SELECT P1.MemberId, SUM(P1.Point),SUM(L1.LessonPoint),L1.MemberId as p1sum FROM POINT P1
INNER JOIN LESSON L1
ON P1.MemberId= L1.MemberID  
GROUP BY P1.MemberId
ORDER BY  MemberId DESC
)
select  MemberId, p1sum - l1sum as res_sum FROm CTE

Upvotes: 0

StanislavL
StanislavL

Reputation: 57421

SELECT P1.MemberId, P1.p1sum -L1.l1sum as res_sum AS PIA
FROM (SELECT P1.MemberId, SUM(P1.Point) as p1sum FROM POINT P1 GROUP BY P1.MemberId) P1
JOIN (SELECT L1.MemberId, SUM(L1.LessonPoint) as l1sum FROM LESSON L1 GROUP BY L1.MemberId) L1
  ON P1.MemberId= L1.MemberID 
ORDER BY P1.MemberId DESC

Just calculate the SUMs in subqueries and JOIN both subqueries

Upvotes: 0

Related Questions