Sql-server – sum several results

oraclesql server

DB Source

Blockquote

how can I add the result of 4 sum, to get this

Blockquote

I tried this query but the total is wrong, the total include eth05 colunm.

Any idea how to fix that? 

select router, count(*) total,
                     sum (CASE WHEN eth01 is not null then 1 ELSE NULL END) as "eth01",
                     sum(CASE WHEN eth02  is not null then 1 ELSE NULL END) as "eth02",
                     sum(CASE WHEN eth03 is not null then 1 ELSE NULL END) as "eth03",
                     sum(CASE WHEN eth04  is not null then 1 ELSE NULL END) as "eth04"                   
       from Speed_report
                  where(eth01 = data or eth02 = data or eth03 = data or eth04 = data )               
                  GROUP BY  router               
                  ;

Best Answer

with t as 
(
  select 'Netgear' as device, null as eth01, 'data' as eth02, null as eth03, null as eth04, 'data' as eth05 from dual union all
  select 'Netgear' as device, null as eth01, 'data' as eth02, 'data' as eth03, null as eth04, 'data' as eth05 from dual union all
  select 'Netgear' as device, null as eth01, 'data' as eth02, null as eth03, null as eth04, 'data' as eth05 from dual union all
  select 'Motorola' as device, 'data' as eth01, 'data' as eth02, null as eth03, 'data' as eth04, 'data' as eth05 from dual union all
  select 'Motorola' as device, 'data' as eth01, 'data' as eth02, 'data' as eth03, 'data' as eth04, 'data' as eth05 from dual union all
  select 'Motorola' as device, 'data' as eth01, 'data' as eth02, null as eth03, 'data' as eth04, 'data' as eth05 from dual union all
  select 'Motorola' as device, null as eth01, 'data' as eth02, 'data' as eth03, null as eth04, 'data' as eth05 from dual union all
  select 'Belk' as device, null as eth01, 'data' as eth02, null as eth03, null as eth04, 'data' as eth05 from dual union all
  select 'Belk' as device, 'data' as eth01, 'data' as eth02, 'data' as eth03, null as eth04, 'data' as eth05 from dual union all
  select 'Belk' as device, 'data' as eth01, 'data' as eth02, null as eth03, 'data' as eth04, 'data' as eth05 from dual union all
  select 'Belk' as device, 'data' as eth01, 'data' as eth02, 'data' as eth03, null as eth04, null as eth05 from dual
)
select
  device,
  count(eth01) as eth01, count(eth02) as eth02, count(eth03) as eth03, count(eth04) as eth04,
  count(eth01) + count(eth02) + count(eth03) + count(eth04) as total
from t
group by device;

DEVICE        ETH01      ETH02      ETH03      ETH04      TOTAL
-------- ---------- ---------- ---------- ---------- ----------
Netgear           0          3          1          0          4
Belk              3          4          2          1         10
Motorola          3          4          2          3         12
Related Question