Sum of columns that are already summarized in sql

Question

SELECT DISTINCT emp_no, sum(CASE WHEN isnull(PresAbs,0)='WO' THEN 1 ELSE 0 END) WO, sum(CASE WHEN isnull(PresAbs,0)='WP' THEN 1 ELSE 0 END) WP, sum(CASE WHEN isnull(PresAbs,0)='HL' THEN 1 ELSE 0 END) HL, sum(CASE WHEN isnull(PresAbs,0)='A' THEN 1 ELSE 0 END) A,**sum(WO+WP+HL+A)** from TRN_ATTN072013 WHERE CONVERT(varchar,Tdate,112)>'20130712' and CONVERT(varchar,Tdate,112)<'20130717' group by emp_no ORDER BY emp_no 

I want to summarize these columns WO, WP, HL, A how to sum these columns

Answer 1

If it is SQL server , you can use CTE

 with tablesum as ( SELECT DISTINCT emp_no, sum(CASE WHEN isnull(PresAbs,0)='WO' THEN 1 ELSE 0 END) WO, sum(CASE WHEN isnull(PresAbs,0)='WP' THEN 1 ELSE 0 END) WP, sum(CASE WHEN isnull(PresAbs,0)='HL' THEN 1 ELSE 0 END) HL, sum(CASE WHEN isnull(PresAbs,0)='A' THEN 1 ELSE 0 END) A from TRN_ATTN072013 WHERE CONVERT(varchar,Tdate,112) > '20130712' and CONVERT(varchar,Tdate,112) < '20130717' group by emp_no ) select t.*, t.WO + t.WP + t.HL + tA from tablesum t order by t.emp_no 

Answer 2

in mysql;)

 SELECT emp_no, wo+wp+hl+A FROM (SELECT DISTINCT emp_no, sum(CASE WHEN isnull(PresAbs,0)='WO' THEN 1 ELSE 0 END) WO, sum(CASE WHEN isnull(PresAbs,0)='WP' THEN 1 ELSE 0 END) WP, sum(CASE WHEN isnull(PresAbs,0)='HL' THEN 1 ELSE 0 END) HL, sum(CASE WHEN isnull(PresAbs,0)='A' THEN 1 ELSE 0 END) A,**sum(WO+WP+HL+A)** from TRN_ATTN072013 WHERE CONVERT(varchar,Tdate,112)>'20130712' and CONVERT(varchar,Tdate,112)<'20130717' group by emp_no ORDER BY emp_no) AS SUB_Q;