MySQL sum() from more than 1 table

SELECT fee +
    sum(((hourEnd-hourStart)*4) as sumAct3 
    from reserve join activity
    on reserve.idactivity=activity.idactivity
    join customer
    on customer.idcustomer=reserve.idcustomer
    where activity.typeAct=1 and customer.idcustomer='S1')
    +
    sum(((hourEnd-hourStart)*2) as sumAct2
    from reserve join activity
    on reserve.idactivity=activity.idactivity
    join customer
    on customer.idcustomer=reserve.idcustomer
    where activity.typeAct=2 and customer.idcustomer='S1')
FROM customer 
WHERE idcustomer='S1';

Ok, so every sum() returns the desired value, I already checked it but I’m having problems when trying to sum everything together and then get the total value.

Basically I have to do this:

• initial Fee(which is a set value defined on the”customer” table) + sum of activity1 cost + sum of activity2 cost == value

But I don’t know how I can make it work so I get a sum of everything, I tried for a while but I always get the “SELECT is not valid at this position”

Answer

You can get the sums of hours more readily by just JOINing the tables to each other and using conditional aggregation to get the activity sums:

SELECT c.idcustomer, 
       SUM(CASE WHEN a.typeAct = 1 THEN hourEnd - hourStart ELSE 0 END) * 4 AS sumAct1,
       SUM(CASE WHEN a.typeAct = 2 THEN hourEnd - hourStart ELSE 0 END) * 2 AS sumAct2
FROM customer c
JOIN reserve r ON r.idcustomer = c.idcustomer
JOIN activity a ON a.idactivity = r.idactivity
WHERE a.typeAct IN (1, 2) AND c.idcustomer = 'S1'
GROUP BY c.idcustomer

And then using this result as a derived table for the final addition:

SELECT c.idcustomer, c.fee + f.sumAct1 + f.sumAct2
FROM customer c
JOIN (
    SELECT c.idcustomer, 
           SUM(CASE WHEN a.typeAct = 1 THEN hourEnd - hourStart ELSE 0 END) * 4 AS sumAct1,
           SUM(CASE WHEN a.typeAct = 2 THEN hourEnd - hourStart ELSE 0 END) * 2 AS sumAct2
    FROM customer c
    JOIN reserve r ON r.idcustomer = c.idcustomer
    JOIN activity a ON a.idactivity = r.idactivity
    WHERE a.typeAct IN (1, 2) AND c.idcustomer = 'S1'
    GROUP BY c.idcustomer
) f ON f.idcustomer = c.idcustomer