Mysql嵌套查询，1个表，3个查询，个别结果

Question

What I have:

2 Tables

- table_benefactor ( id and benefactor names)
- table_realstate  (house adress, rent, paidrent flag)

If paidrent = 0 (unpaid)
If paidrent = 1 (paid)

What I need:

1 nested query that returns me a result like below

name | totalrent | paid | unpaid
_________________________________
Jhon | 1,000.00  |100.00| 900.00
Doe  | 2,500.00  |500.00| 2,000.00
Chris| 800.00    |0.00  | 800.00

What I have Tried:

I tried many ways but non of them gives me the result I need, they get close but not what I need, This are my queries ->

If I do it like this it will return exactly what I want but not in the way I need it

(SELECT table_realstate.benefactor, name, SUM(rent) totalrent 
FROM table_realstate, table_benefactor 
WHERE flag = 0 
AND table_realstate.benefactor = table_benefactor.id
GROUP BY benefactor
ORDER BY name ASC) 
UNION ALL
(SELECT table_realstate.benefactor, name, SUM(rent) unpaid 
FROM table_realstate, table_benefactor 
WHERE flag = 0 
AND table_realstate.benefactor = table_benefactor.id 
AND table_realstate.paidrent = 0
GROUP BY benefactor
ORDER BY name ASC) 
UNION ALL
(SELECT table_realstate.benefactor, name, SUM(rent) paid 
FROM table_realstate, table_benefactor 
WHERE flag = 0 AND table_realstate.benefactor = table_benefactor.id 
AND table_realstate.paidrent = 1
GROUP BY benefactor
ORDER BY name ASC)

Result:

name | totalrent 
_________________
Jhon | 1,000.00  
Doe  | 2,500.00  
Chris| 800.00    
Jhon | 100.00  
Doe  | 500.00 
Jhon | 900.00 
Doe  | 2,000.00 
Chris| 800.00   

So I have been trying something like this:

If I do it like this the structure of the result is what I need but the math and the benefactor names comes out wrong ->

SELECT table_benefactor.name, SUM(rent) totalrent, SUM(rent) unpaid, SUM(rent) paid
    FROM table_realstate, table_benefactor,
    (SELECT table_realstate.benefactor, name, SUM(rent) totalrent 
    FROM table_realstate, table_benefactor 
    WHERE flag = 0 
    AND table_realstate.benefactor = table_benefactor.id 
    UNION ALL
    SELECT table_realstate.benefactor, name, SUM(rent) unpaid 
    FROM table_realstate, table_benefactor 
    WHERE flag = 0 
    AND table_realstate.benefactor = table_benefactor.id 
    AND table_realstate.paidrent = 0 
    UNION ALL
    SELECT table_realstate.benefactor, name, SUM(rent) paid 
    FROM table_realstate, table_benefactor 
    WHERE flag = 0 AND table_realstate.benefactor = table_benefactor.id 
    AND table_realstate.paidrent = 1) abc 
GROUP BY table_realstate.benefactor

Result:

name | totalrent | paid | unpaid
_________________________________
Jhon | 119.200   |119.200  | 119.200
Jhon | 1,800.02  |1,800.02 | 1,800.02
Jhon | 29,1964.2 |29,1964.2| 29,1964.2
Jhon | 27,000.00 |27,000.00| 27,000.00

as you can see the benefactor name repeats itself and the values are all over the place.

I've tried other stuff but don't want to make the post any longer than it already is.

Answer 1

I think you just want conditional aggregation. If the paidrent flag only takes on the values 0 and 1, as suggested by your code, then this is particularly simple:

select b.name, sum(re.rent) as total_rent,
       sum(re.rent * re.paidrent) as paid,
       sum(re.rent * (1 - re.paidrent)) as paid,
from table_benefactor b join
     table_realestate re
     on b.id = re.benefactor
group by b.id, b.name;