How to subtotal a result while retaining detail (SQL Server/MySQL)

I recently needed to produce a query with detail and have it subtotal by employee, by date. Subtotals can be difficult if you have data that varies within the subtotalled group.

The work around is to union queries. One query with the detail and another query with the subtotal by the control break you need. We then use a case condition in the order by clause to cause our subtotal to appear where we want it in the result.

The following example will work in SQL Server or MySQL.

 
SELECT fname+' '+lname AS Name, act_date, task_code, task_type, descr, hours, name, billable
FROM
(
            SELECT E.fname, E.lname, ET.act_date, cast(ET.task_code as varchar) task_code, T.task_type, T.descr, ET.hours, C.name, T.billable, user_id
            FROM empl_time ET
            LEFT JOIN Task T ON ET.task_code = T.task_code
            LEFT JOIN Employee E on ET.emp_code = E.emp_code
            LEFT JOIN Customer C on T.customer_code = C.customer_code
 
            UNION
 
            SELECT E.fname, E.lname, ET.act_date, '', '', '*** Total ***', SUM(ET.hours), '', '', user_id
            FROM empl_time ET
            LEFT JOIN Task T ON ET.task_code = T.task_code
            LEFT JOIN Employee E on ET.emp_code = E.emp_code
            LEFT JOIN Customer C on T.customer_code = C.customer_code
 
            group by E.fname, E.lname, ET.act_date, user_id
) x
 
ORDER BY lname, act_date, case when task_code = '' then 2 else 1 end 

Copy Code

References

SQL Server Select
SQL Server From
SQL Server Left Join
SQL Server Group By
SQL Server Order By
SQL Server Cast / Convert