I have 3 tables Here the location http://rextester.com/PED43367
I failed in with roll up, can some one giving me the way?
the output i want is :
The result is ok, but I can't make rollup with that Thanks for your Help
You should read up on mysql order of execution (MySQL query / clause execution order) and https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html paying particular attention to "If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want."
In brief then your from and joins are executed first, the group by is dodgy and the rollup adds issues.
I would separate out the group by for pivot and then add the joins using group by "properly"
select m.codes,m.version,sum(m.headcount) headount,sum(m.reghrs) reghrs, sum(m.hrsbdgt) hrsbudget,
sum(w.workhrs) workhours, sum(w.reghrs) wreghrs,
sum(d1) '02-04-2017',
sum(d2) '09-04-2017',
sum(d3) '16-04-2017',
sum(d4) '23-04-2017',
sum(d5) '30-04-2017',
sum(p.hours) as Total,
SUM(p.hours) - sum(m.HrsBdgt) RsltBdgt
from mtarget m
left join
(
select CODEPivot,categoryPivot ,
SUM(IF(pivot.selesai = '2017-04-02',pivot.hours,0)) d1,
SUM(IF(pivot.selesai = '2017-04-09',pivot.hours,0)) d2,
SUM(IF(pivot.selesai = '2017-04-16',pivot.hours,0)) d3,
SUM(IF(pivot.selesai = '2017-04-23',pivot.hours,0)) d4,
SUM(IF(pivot.selesai = '2017-04-30',pivot.hours,0)) d5,
sum(pivot.hours) hours
from pivot
group by CODEPivot,categoryPivot
) p on
m.codeS = p.CODEPivot
and m.version = p.categoryPivot
left join whweek w on
w.Code = p.CODEPivot
and w.version = p.CategoryPivot
group by codes,version with rollup
+-------+---------+----------+--------+-----------+-----------+---------+------------+------------+------------+------------+------------+-------+----------+
| codes | version | headount | reghrs | hrsbudget | workhours | wreghrs | 02-04-2017 | 09-04-2017 | 16-04-2017 | 23-04-2017 | 30-04-2017 | Total | RsltBdgt |
+-------+---------+----------+--------+-----------+-----------+---------+------------+------------+------------+------------+------------+-------+----------+
| FII | YAA | 3 | 432 | 35 | 144 | 432 | 28 | 28 | 14 | 24 | 41 | 135 | 100 |
| FII | NULL | 3 | 432 | 35 | 144 | 432 | 28 | 28 | 14 | 24 | 41 | 135 | 100 |
| IDS | YAA | 3 | 432 | 35 | 144 | 432 | 8 | 0 | 0 | 0 | 0 | 8 | -27 |
| IDS | NULL | 3 | 432 | 35 | 144 | 432 | 8 | 0 | 0 | 0 | 0 | 8 | -27 |
| RRT | BKK | 1 | 144 | 12 | 144 | 144 | 8 | 3 | 16 | 15 | 32 | 74 | 62 |
| RRT | WESEL | 1 | 144 | 12 | 144 | 144 | 0 | 14 | 7 | 2 | 0 | 23 | 11 |
| RRT | YAA | 9 | 1296 | 104 | 144 | 1296 | 67 | 98 | 135 | 103 | 119 | 522 | 418 |
| RRT | NULL | 11 | 1584 | 128 | 432 | 1584 | 75 | 115 | 158 | 120 | 151 | 619 | 491 |
| NULL | NULL | 17 | 2448 | 198 | 720 | 2448 | 111 | 143 | 172 | 144 | 192 | 762 | 564 |
+-------+---------+----------+--------+-----------+-----------+---------+------------+------------+------------+------------+------------+-------+----------+
9 rows in set (0.00 sec)