MySQL sum() from more than 1 table

Question

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"

score 2 · Accepted Answer · answered Dec 01 '19 at 04:44

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

MySQL sum() from more than 1 table

1 Answers1