mysql get rollup value

Question

I want to ask about mysql with rollup only get rollup value, this is my code

SELECT tbldept.CodeDept AS Dept,
       SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) AS headcount,
       SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 AS RegHrsEst,
       CONCAT(ROUND(20), '%') AS TargetOT,
       round(SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 * 0.2) AS OTHrsBdgt
FROM tbldept
LEFT JOIN tblemp
    ON tbldept.CodeDept = tblemp.CodeDept AND
       tblemp.class = "DIR"
WHERE tbldept.CodeDept != 'FIN' AND
      tbldept.CodeDept != 'HRT' AND
      tbldept.CodeDept != 'ISD'
GROUP BY tbldept.CodeDept with ROLLUP;

result of rollup query:

how can i only get the rollup values? Thanks before

Answer 1

If you just want the row of rollup, simply use having:

SELECT tbldept.CodeDept AS Dept,
       SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) AS headcount,
       SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 AS RegHrsEst,
       CONCAT(ROUND(20), '%') AS TargetOT,
       round(SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 * 0.2) AS OTHrsBdgt
FROM tbldept
LEFT JOIN tblemp
    ON tbldept.CodeDept = tblemp.CodeDept AND
       tblemp.class = "DIR"
WHERE tbldept.CodeDept != 'FIN' AND
      tbldept.CodeDept != 'HRT' AND
      tbldept.CodeDept != 'ISD'
GROUP BY tbldept.CodeDept with ROLLUP
HAVING Dept IS NULL;

Edit:

having should work, check if there are some other reasons exist or not.

Or just wrap your query with a subquery:

SELECT *
FROM (
    SELECT tbldept.CodeDept AS Dept,
           SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) AS headcount,
           SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 AS RegHrsEst,
           CONCAT(ROUND(20), '%') AS TargetOT,
           round(SUM(CASE WHEN tblemp.Status = '1' THEN 1 ELSE 0 END) * 144 * 0.2) AS OTHrsBdgt
    FROM tbldept
    LEFT JOIN tblemp
        ON tbldept.CodeDept = tblemp.CodeDept AND
           tblemp.class = "DIR"
    WHERE tbldept.CodeDept != 'FIN' AND
          tbldept.CodeDept != 'HRT' AND
          tbldept.CodeDept != 'ISD'
    GROUP BY tbldept.CodeDept with ROLLUP
) t
WHERE Dept IS NULL;