Sum from rows and difference between total and rows

Question

After a sql select query the array that I'm getting is the one written down, to this array i would like to add a sum(values) AS Total_Values column and a sum(values) - values AS Diff_Values column, I tried multiple ways but i haven't got the right thing

the result of my current query is something like:

Car_Number  Start_time  Code_nr Values  Total_Values
BH-07-EWR   08:59:00    C00425  7000    44400
BH-07-EWR   10:29:00    C00149  8500    44400
BH-07-EWR   14:27:00    C01075  9200    44400
BH-07-EWR   15:07:00    C00305  9800    44400
BH-07-EWR   16:08:00    C02572  9900    44400

And I would like it to be:

Car_Number  Start_time  Code_nr Values  Total_Values    Diff_Values
BH-07-EWR   08:59:00    C00425  7000    44400           44400
BH-07-EWR   10:29:00    C00149  8500    44400           37400
BH-07-EWR   14:27:00    C01075  9200    44400           28900
BH-07-EWR   15:07:00    C00305  9800    44400           19700
BH-07-EWR   16:08:00    C02572  9900    44400           9900

How can I achieve this? Things may look easy but after a bit more complicated for my experience. Thanks

Update:

Example SQL:

SELECT *, SUM(MINUTE(rest_time)) AS Times FROM routes as routes 
LEFT JOIN sales ON (routes.departure_poi_code = sales.client_code 
                AND routes.departure_date = sales.date) 
WHERE (routes.departure_poi_code LIKE 'C0%' 
       OR routes.departure_poi_code LIKE 'MP%') 
      AND routes.car_no = 'BH-07-EWR' 
      AND routes.departure_date = '2011-10-14' 
GROUP BY routes.departure_address, reports.routes.departure_poi_code, reports.routes.car_no, reports.routes.departure_date 
ORDER BY routes.car_no LIMIT 500000 ; 

of course the query returns more columns that are presented up.

Answer 1

Assume that your table name is TBL.

SELECT TBL.*, Total_Values, @R:=IF(@R=0, Total_Values, @R-TBL.`Values`) AS Diff_Values
  FROM TBL, ( SELECT @R:=0, SUM(`Values`) AS Total_Values FROM TBL ) x;

Answer 2

This indeed is tricky to do. I can't make out how to do it in a query but perhaps a stored procedure might be a way.

Assume that the below result set from your query is stored in an intermediate table called TMPTBL where each row is identified by a unique id. So TMPTBL will look like this:

id  Car_Number  Start_time  Code_nr Values
1   BH-07-EWR   08:59:00    C00425  7000
2   BH-07-EWR   10:29:00    C00149  8500
3   BH-07-EWR   14:27:00    C01075  9200
4   BH-07-EWR   15:07:00    C00305  9800
5   BH-07-EWR   16:08:00    C02572  9900

Note that you do not need to calculate Total_Values that will be done as part of the stored procedure.

Now create a procedure as below:

DELIMITER ;;
CREATE PROCEDURE spCompute()
BEGIN
    DECLARE idn INT;
    DECLARE vals INT;
    DECLARE totVals INT DEFAULT 0;
    DECLARE noData INT DEFAULT FALSE;
    DECLARE csrData CURSOR FOR SELECT `id`, `Values` FROM `TMPTBL` ORDER BY `id` DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET noData = TRUE;

    OPEN csrData;
    startLoop: LOOP
        FETCH csrData INTO idn, vals;
        IF noData THEN
            LEAVE startLoop;
        END IF;
        SET totVals = totVals + vals;
        UPDATE `TMPTBL` SET `Diff_Values` = totVals WHERE `id` = idn;
    END LOOP;
    UPDATE `TMPTBL` SET `Total_Values` = totVals;
    CLOSE csrData;
END;;
DELIMITER ;

The idea is that rather than doing the diffs, the procedure adds the values instead. Now if you run a SELECT query on TMPTBL, you'll see the Diff_Values and Total_Values. Please note that I haven't tested the procedure but I think it should work.

Few things to note:

1. I've assumed that TMPTBL has an id column
2. that you do not need to calculate Total_Values in the query
3. that table TMPTBL already exists and contains all columns from your query except Total_Values

Hope it helps!