SQL SELECT multiple data in table and calculate

Question

I want to SELECT and calculate with multiple SELECT statements.

The SQL statements I want to run are:

SELECT COALESCE(SUM(left), 0) - COALESCE(SUM(right), 0) as total1
FROM trans
WHERE user_id = 2 AND k_id = 120;

SELECT COALESCE(SUM(left), 0) - COALESCE(SUM(right), 0) as total2
FROM trans
WHERE user_id = 2 AND k_id = 121;

SELECT COALESCE(SUM(left), 0) - COALESCE(SUM(right), 0) as total3
FROM trans
WHERE user_id = 2 AND k_id = 480;

After the results are selected I want to sum the total (total1 + total2 + total3). Does someone know if this is possible in mysql?

My table structure looks like:

CREATE TABLE trans
    (`id` int, 
    `user_id` int(11), 
    `f_id` varchar(55), 
    `k_id` int(11), 
    `left` varchar(55), 
    `right` varchar(55))
;

INSERT INTO trans
    (`id`, `user_id`, `f_id` , `k_id`, `left`, `right`)
VALUES
    (1, '2', 'F/12C', '120', '0', '1250.00'),
    (2, '2', 'F/1B3', '121', '0', '225.50'),
    (3, '2', 'F/1B3', '654', '0', '26.50'),
    (4, '2', 'F/1B3', '612', '0', '12.50'),
    (5, '2', 'F/A23', '480', '1512.50', '112.00'),
    (6, '2', 'F/A23', '412', '112.50', '742.00'),
    (7, '2', 'F/1D3', '220', '0', '50.00')
;

Answer 1

I think you can do what you want using GROUP BY WITH ROLLUP:

SELECT k_id, COALESCE(SUM(left), 0) - COALESCE(SUM(right), 0) as total1
FROM trans
WHERE user_id = 2 AND k_id IN (120, 121, 480)
GROUP BY k_id WITH ROLLUP;