Why does using with rollup give me different results when inside pivot are using sum and pivot and left join?

Question

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 :

enter image description here

The result is ok, but I can't make rollup with that Thanks for your Help

Answer 1

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)