how to counting data from each column in MySQL?

Question

i have some mysql script for pivot table and then counting some data inside that:

SELECT A.Line,
       week1.1stweek, week2.2ndweek,
       IFNULL(week1.1stweek,0) + IFNULL(week2.2ndweek,0) AS TOTAL
FROM inspection_report AS A
LEFT JOIN(
           SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 1stweek
           FROM inspection_report
           WHERE DAY(Inspection_datetime) BETWEEN 1 AND 7
           GROUP BY Line, WEEK(Inspection_datetime), YEAR(Inspection_datetime)
          ) AS week1 USING (Line)
LEFT JOIN(
           SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 2ndweek
           FROM inspection_report
           WHERE DAY(Inspection_datetime) BETWEEN 8 AND 14
           GROUP BY Line, WEEK(Inspection_datetime), YEAR(Inspection_datetime)
          ) AS week2 USING (Line)
GROUP BY Line

it makes the table head show like button. after that i want to make a "SUM" below each column like:

Line          1stweek            2ndweek             total
1               12                 2                   14
2               3                  0                    3
SUM             15                 2                   17

how do i do that?

Answer 1

Use the WITH ROLLUP modifier.

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

You'd have to modify the outer select statement though to

SELECT A.Line,
       sum(week1.1stweek) as 1stweek, sum(week2.2ndweek) 2ndweek,
       sum(IFNULL(week1.1stweek,0) + IFNULL(week2.2ndweek,0)) AS TOTAL

for the modifier to take effect. Change A.Line to IFNULL(A.Line, "SUM") as Line instead if you need it to be labelled as such.

Answer 2

This interested me, so I implemented a equivalent in an online SQL sandbox, here.

select ifnull(toy, "SUM") as toy, sum(s1.store1) as store2, sum(s2.store2) as store1, sum(ifnull(store1,0)+ifnull(store2,0)) as total from (select toy_id, sum(inventory) as store1 from store_inventory where store_id=1 group by toy_id) as s1 left join (select toy_id, sum(inventory) as store2 from store_inventory where store_id=2 group by toy_id) as s2 using (toy_id) left join toy_info using (toy_id) group by toy with rollup;

(Pardon my lack of markdown skills, this is my first post.)