MySQL - Complicated SUMs inside Query

Question

This is going to be tough to explain.

I'm looping through my client records from tbl_customers several times a day.

SELECT c.* FROM tbl_customers c

I'm returning simply the customer's: customerid, name, phone, email

Now the weird part. I want to append 3 more columns, after email: totalpaid, totalowed, totalbalance BUT, Those column names don't exist anywhere.

Here is how I query each one: (as a single query)

SELECT SUM(total) AS totalpaid 
FROM tbl_customers_bills 
WHERE customerid = X
AND billtype = 1

SELECT SUM(total) AS totalowed 
FROM tbl_customers_bills 
WHERE customerid = X
AND billtype = 2

SELECT SUM(total) AS totalbalance 
FROM tbl_customers_bills 
WHERE customerid = X
AND billtype IN(1,2)

So, the billtype is the column that tells me whether the record is paid or not.

I am at a loss here. How can I SUM 3 separate queries into the first query's loop?

score 5 · Accepted Answer · edited May 23 '17 at 12:27

Just join customers to bills and do the sums. To separate out totalpaid and totalowed you can use SUM(CASE or SUM(IF as wless1's answer demonstrates

SELECT c.*,
        SUM(CASE WHEN billtype = 1 THEN total ELSE 0 END) totalpaid ,
        SUM(CASE WHEN billtype = 2 THEN total ELSE 0 END) totalowed ,
        SUM(total) AS totalbalance
FROM 
    tbl_customers c
    LEFT JOIN tbl_customers_bills  b
    ON c.customerid = b.customerid
     and billtype in (1,2)
GROUP BY 
     c.customerid

Because this is MySQL you only need to group on the PK of customer.

score 3 · Answer 2 · answered Jan 09 '13 at 23:35

You could do this with a combination of GROUP, SUM, and IF

SELECT c.id, c.name, c.phone, c.email, 
SUM(IF(b.billtype = 1, b.total, 0)) AS totalpaid,
SUM(IF(b.billtype = 2, b.total, 0)) AS totalowed,
SUM(IF(b.billtype = 1 OR b.billtype = 2, b.total, 0)) AS totalbalance,
FROM tbl_customers c LEFT JOIN tbl_customers_bills b ON b.customerid = c.id
GROUP BY c.id

See: http://dev.mysql.com/doc/refman/5.0/en//group-by-functions.html http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

MySQL - Complicated SUMs inside Query

2 Answers2

Linked