select data along with sum of a column

Question

What would be the best sql to get data out from the table below as follows

Example table

   id   name        buyer    amt
    1   per disney   10
    1   per marie    12
    1   per clarence 11
    2   pella   Magnus    5
    2   pella   Olof      2
    2   pella   Asa       4
    3   chris   Lotta     6
    3   chris   Wayne     3
    3   chris   Brad      5

Data output:

Customer Name : Per
disney    10
marie     12
clarence  11
Total     33

Customer Name : pella
Magnus    5
Olof      2
Asa       4
Total    11

Customer Name : Chris
Lotta     6
Wayne     3
Brad      5
Chris    14

So in the end its the name at the top, and every buyer for that seller and each transaction and finally the total.

I came upwith this sql query, but its rather crude.

select name, supplier, amt, (select sum(amt) from transact where name = t.name)as total from transact t

Answer 1

Use Group By and with Rollup to get the sums.

select name, supplier, amt
from transact t
group by name, supplier with rollup;

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