Getting ROLLUP or sum of Count() in a new filed in mySQL

Question

i have a table where i need count for a column and then group it by other column, and then get the total value of count per group in a new field beside the count. i am adding some data to explain exactly what i want:

   CREATE TABLE tablename (
  ID INTEGER,
  NAME VARCHAR (10),
  TAG1 INTEGER,
  TAG2 INTEGER
);

INSERT INTO tablename VALUES 
(1, 'A', 1, 2),
(1, 'A', 2, 5),
(1, 'B', 1, 4),
(1, 'D', 3, 7),
(1, 'C', 5, 6),
(2, 'A', 1, 2),
(2, 'A', 2, 5),
(2, 'A', 2, 5),
(2, 'B', 1, 4),
(2, 'D', 3, 7),
(2, 'C', 5, 6),
(3, 'A', 1, 2),
(3, 'A', 2, 5),
(3, 'B', 1, 4),
(3, 'D', 3, 7),
(3, 'A', 1, 2),
(4, 'A', 2, 5),
(4, 'B', 1, 4),
(4, 'D', 3, 7),
(4, 'D', 5, 6),  
(4, 'C', 5, 6);

i have used query:

select ID
     , ifnull(NAME,Concat(ifnull(NAME,'TOTAL - '), ID)) NAMES
     , sum(case when (TAG1>0 and TAG2>0) then 1 else 0 end) Cnt 
  from tablename 
 group 
    by ID
     , NAME with rollup;

what i get is :

ID NAME Cnt 
1   A    2
1   B    1
1   C    1
1   D    1
1  Total 5
2   A    3
2   B    1
2   C    1
2   D    1
2  Total 6
3   A    3
3   B    1
3   D    1
3  Total 5

and what i want is sum() field beside the count field

something like

ID NAME Cnt Sum
1   A    2   5
1   B    1   5
1   C    1   5
1   D    1   5
1  Total 5   5
2   A    3   6
2   B    1   6 
2   C    1   6
2   D    1   6
2  Total 5   6
3   A    3   5
3   B    1   5
3   D    1   5
3  Total 5   5

any suggestions???

Answer 1

This is an unusual request. You cannot get what you want using sum() for the third column because of the rollup. So, use a subquery:

select t.ID, coalesce(NAME, concat('TOTAL - ', t.ID), t.ID)) as NAMES,
       sum(t.TAG1 > 0 and t.TAG2 > 0) as Cnt,
       max(tt.sumcnt) as `sum`
from tablename t join
     (select id, sum(TAG1 > 0 and TAG2 > 0) as sumcnt
      from tablename t
      group by id
     ) tt
     on t.id = tt.id
group by t.ID, t.NAME with rollup;