How query two different Sum from the same table for the same column but with different condition?

I would like to fetch the sum of field TOTAL for active users and sum of field TOTAL for inactive users.

id userId active total
 1 1         1     10
 2 1         1     5
 3 1         0     30
 4 1         0     20

Expecting is the query that results activeTotal = 15 and InactiveTotal = 50

SELECT SUM(actUser.total) as activeTotal, SUM(inActUser.total) as inactiveTotal FROM user actUser JOIN user inActUser ON inActUser.id = inActUser.id WHERE (actUser.active = 1 AND actUser.userId = 1) OR (inActUser.active = 0 AND inActUser.userId= 1)

But I am not getting the expected result….I am getting the same number for inactive user as getting for inactive users.

Answer

You can use conditional aggregation:

select
    sum(case when active = 1 then total else 0 end) total_active,
    sum(case when active = 0 then total else 0 end) total_inactive
from mytable

If you have just 0s and 1s in column active, we can simplify:

select
    sum(active * total) total_active,
    sum( (1 - active) * total) total_inactive
from mytable