Question 308716 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query to compare SUM of two columns from two different tables

I am developing a website for a charity where amounts are pledged by individuals and recorded into a table. The individuals belong to a group. The payments received are recorded in another table however they are tagged to a group leader and not an individual. There can be multiple payments to reach the pledged amount.
I wanted an SQL query that would compare the amounts pledged by a group with the payments tagged to the group leader. My Tables are as follows:

Donor Table

id group_leader_id name
1001 1001 John
1002 1001 Steve
1003 1001 Richard
1004 1004 Paul
1005 1004 Stacy
1006 1004 Lucy

Pledged Table

id amount year
1001 20 2023
1002 25 2023
1003 10 2023
1004 15 2023
1005 40 2023
1006 50 2023

Payment Table

id amount year
1001 10 2023
1001 10 2023
1001 20 2023
1001 15 2023
1004 35 2023
1004 35 2023
1004 35 2023

I have created two queries with sum but how can i compare them to check if the amount that has been pledged has been paid?

query1

SELECT Donor.Name Donor.id , SUM(Pledged.amount) 
FROM Donor , Pledged
WHERE Donor.id = Pledged.id
AND year = '2023'
GROUP BY group_leader_id

query2

SELECT Donor.id , SUM(Payment.amount) 
FROM Payment
WHERE year = '2023'
GROUP BY Donor.id
Tags:

4

Answers


  1. 0

    Here is An Example:

    SELECT
    Donor.group_leader_id AS group_leader,
    SUM(Pledged.amount) AS total_pledged,
    SUM(Payment.amount) AS total_paid,
    CASE WHEN SUM(Pledged.amount) = SUM(Payment.amount) THEN 'Paid in Full' ELSE 'Pending Payment' END AS payment_status
FROM Donor
JOIN Pledged ON Donor.id = Pledged.id
JOIN Payment ON Donor.group_leader_id = Payment.id
WHERE Donor.year = '2023' AND Payment.year = '2023'
GROUP BY Donor.group_leader_id
ORDER BY Donor.group_leader_id;

    This query provides a clear comparison of pledged and paid amounts for each group, making it easy to track payment status and identify any outstanding balances.

    Login or Signup to reply.
  2. 0

    You can JOIN the two queries like this:

    SELECT query1.group_leader_id,
       Pledged,
       Payment,
       Pledged - Payment   as  Difference
FROM (
   -- query1 (corrected)
   SELECT group_leader_id , SUM(Pledged.amount) as Pledged
   FROM Donor 
   INNER JOIN Pledged ON Donor.id = Pledged.id
   WHERE  year = '2023'
   GROUP BY group_leader_id
  ) query1
INNER JOIN (
   -- query2 (corrected)
   SELECT id , SUM(Payment.amount) as Payment
   FROM Payment
   WHERE year = '2023'
   GROUP BY id
) query2  ON query1.group_leadeR_id = query2.id

    see: DBFIDDLE

    You might need to change some small things, but that’s because no desired output was given in your question …. 😢

    Login or Signup to reply.
  3. 0

    With this structure you can’t assign a payment to an individual pledge.

    This means that your aggregate output would presumably be the amount Pledged per group leader, vs the amount Paid per group leader.

    That I need to presume that is one reason you’ve gotten such poor engagement for your question. Please ensure you include the expected results in your questions, as per the [mre] help page.

    WITH
  pledge (
    donor_id,
    donor_name,
    year,
    pledge_amount
  )
AS
(
  SELECT
    d.group_leader_id,
    MAX(CASE WHEN d.id = d.group_leader_id THEN d.Name END),
    p.year,
    SUM(p.amount)
  FROM
    donor     AS d
  INNER JOIN
    pledged   AS p
      ON d.id = p.id
  GROUP BY
    d.group_leader_id,
    p.year
),
  payment (
    donor_id,
    year,
    payment_amount
  )
AS
(
  SELECT 
    p.id,
    p.year,
    SUM(p.amount) 
  FROM
    payment  AS p
  GROUP BY
    p.id,
    p.year
)
SELECT
  pledge.*,
  COALESCE(payment.payment_amount, 0)
FROM
  pledge
LEFT OUTER JOIN
  payment
    ON  pledge.donor_id = payment.donor_id
    AND pledge.year     = payment.year
WHERE
  pledge.year = 2023

    https://dbfiddle.uk/0u76nmpv

    Login or Signup to reply.
  4. 0 
    with
  t as (
    select
      d.group_leader_id as gid,
      max(case 
            when d.id = d.group_leader_id then d.name
          end) as leader,
      sum(p.amount) as pledged
    from donor as d
    left join pledged as p
    on p.id = d.id
    group by d.group_leader_id
  )
select
  t.*,
  sum(p.amount) as paid
from t
left join payment as p
on p.id = t.gid
group by
  t.gid, t.leader, t.pledged;

    Result:

    +------+--------+----------+----------+
| gid  | leader | pledged  |   paid   |
+------+--------+----------+----------+
| 1001 | John   | 55.0000  | 55.0000  |
| 1004 | Paul   | 105.0000 | 105.0000 |
+------+--------+----------+----------+

    db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search