SELECT     D.domainid AS departid, C.name AS Company, C.companyid AS Companyid, D.name AS departdesc, COUNT(E.empcode) AS employees, 
                      SUM(E.monthdev) AS monthdev, SUM(E.totaldev) AS totaldev, S.shift_descr AS shiftdesc
FROM         tb_T5_ShiftDomain AS SD INNER JOIN
                      tb_S57_Domains AS D ON SD.domainid = D.domainid INNER JOIN
                      tb_T5_Shifts AS S ON SD.shiftid = S.shift_id LEFT OUTER JOIN
                      tb_S57_Companies AS C INNER JOIN
                      tb_S57_CompanyDomains AS CD ON C.companyid = CD.companyid ON D.domainid = CD.domainid LEFT OUTER JOIN
                      tb_T5_MainView AS E ON D.domainid = E.departmentid RIGHT OUTER JOIN
                      tb_T5_AuxInfo AS A ON E.empcode = A.userid
WHERE     (A.dismissed = 0)
GROUP BY D.domainid, C.companyid, S.shift_descr