0

Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last row. How can I remove those data. I provided an image on what I really needed to achieve.

Here is my query.

    SELECT  IFNULL(payments.id, "General Total"), payments.driver_id,
        payments.vehicle_specifications_id,payments.admins_id,
        payments.vehicle_id, payments.boundaries, payments.cashbond_payments,
        payments.loans, payments.penalties, SUM(payments.total_payments),
        a.first_name, a.mid_name, a.last_name, d2.date, d.first_name,
        d.mid_name, d.last_name, v.plate_number, v2.car_rate
    FROM  payments
    INNER JOIN  admins AS     a  ON payments.admins_id = a.id
    INNER JOIN  drivers AS    d  ON payments.driver_id = d.id
    INNER JOIN  dispatch AS   d2 ON payments.dispatch_id =d2.id
    INNER JOIN  vehicles AS   v  ON payments.vehicle_id = v.id
    INNER JOIN  vehicle_specifications AS v2
                ON payments.vehicle_specifications_id = v2.id
    GROUP BY  payments.id WITH ROLLUP

enter image description here

0

Plan A (rather tedious):

IFNULL(payments.id, "General Total"),
IF(payments.id IS NULL, '', payments.driver_id),  -- conditionally blank each
...

Plan B:

( SELECT ... )  -- without ROLLUP
UNION ALL
( SELECT 'Grand Total', '', ... SUM(...), '', ... )  -- Just total
ORDER BY ...   -- to get "Grand Total" to be last

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.