CODEWITHSUNDEEP
mysqlsum
ccdavies
ccdavies

Reputation: 1606

SQL SUM and comparing

I have two database tables:

***aff_purchases***

id | affiliate_id | payout
1  | 12           | 50.00 
2  | 12           | 10.00
3  | 12           | 50.00
4  | 12           | 10.00

***aff_payments***

id | affiliate_id | amount_paid   
8  | 12           | 50.00

I would like to return an array of all affiliate IDs where the 'payout' total is 50 or more than the 'amount_paid' for an affiliate ID.

I think that I need to SUM together the columns and then compare, but I am struggling to understand how. Please see my efforts below:

SELECT
     (SELECT SUM(amount_paid) FROM exp_cdwd_aff_payments AS pay WHERE pay.affiliate_id = 12) AS 'amount_paid'
     (SELECT SUM(payout) FROM exp_cdwd_aff_purchases AS pur WHERE pur.affiliate_id = 12) AS 'payout'
FROM 
     exp_cdwd_aff_payments AS pay
WHERE 
     payout > amount_paid

Upvotes: 0

Views: 246

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522094

One approach here is to use join two separate subqueries which find the payout and payment totals. Then, compare each affiliate_id to see if meets your requirement.

SELECT
    t1.affiliate_id
FROM
(
    SELECT affiliate_id, SUM(amount_paid) AS amount_paid_total
    FROM aff_payments
    GROUP BY affiliate_id
) t1
LEFT JOIN
(
    SELECT affiliate_id, SUM(payout) AS payout_total
    FROM aff_purchases
    GROUP BY affiliate_id
) t2
    ON t1.affiliate_id = t2.affiliate_id
WHERE COALESCE(t2.payout_total, 0) > t1.amount_paid_total + 50

Note that affiliates who have a payout, but have not paid, would not appear in the result set.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The problem is that you cannot use aliases defined in the SELECT in the WHERE. In most databases, you would use a CTE or subquery. However, MySQL does not support CTEs and it imposes overhead on subqueries (by materializing them).

So, MySQL has overloaded the HAVING clause, to allow it to be used in non-aggregation queries. You can do what you want using HAVING:

SELECT a.affiliate_id,
       (SELECT SUM(cap.amount_paid) FROM exp_cdwd_aff_payments cap WHERE cap.affiliate_id = a.affiliate_id) AS amount_paid
       (SELECT SUM(pur.payout) FROM exp_cdwd_aff_purchases pur WHERE pur.affiliate_id = a.affiliate_id) AS payout
FROM affiliates a
HAVING payout > amount_paid;

The above assumes you have a table with one row per affiliate_id. It uses this via a correlated subquery. Also note the use of table aliases and qualified column names.

Upvotes: 0

Related Questions