0

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               
                  ;
0
1
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
1
  • Bravo, it works. Thanks for your help
    – Driven
    May 30 '20 at 23:43
0

I prefer Balaszs' solution which is more direct and more simple, but it is rather easy to adapt your solution (I copied the with clause from Balaszs).

Your "total" is not wrong because it includes the "eth05" column. This column has no influence on the result set of the query. "total" is the count of all rows in a group, so the number of devices of a special type that have at least one of "eth01" or "eth02" or "eth03" or "eth04" equal to 'data'.

create a table with test data:

create table Speed_report
as
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 * from t
;

Now try either

select device, 
    sum(CASE WHEN eth01 is not null then 1 ELSE 0 END) as "eth01",
    sum(CASE WHEN eth02 is not null then 1 ELSE 0 END) as "eth02",
    sum(CASE WHEN eth03 is not null then 1 ELSE 0 END) as "eth03",
    sum(CASE WHEN eth04 is not null then 1 ELSE 0 END) as "eth04",                   
    sum((CASE WHEN eth01 is not null then 1 ELSE 0 END) 
        + (CASE WHEN eth02 is not null then 1 ELSE 0 END)
        + (CASE WHEN eth03 is not null then 1 ELSE 0 END) 
        + (CASE WHEN eth04 is not null then 1 ELSE 0 END)) as "total"                   
    from t
    where(eth01 = 'data' or eth02 = 'data' or eth03 = 'data'  or eth04 = 'data')               
    GROUP BY  device

or

select device, 
    sum(CASE WHEN eth01 is not null then 1 ELSE 0 END) as "eth01",
    sum(CASE WHEN eth02 is not null then 1 ELSE 0 END) as "eth02",
    sum(CASE WHEN eth03 is not null then 1 ELSE 0 END) as "eth03",
    sum(CASE WHEN eth04 is not null then 1 ELSE 0 END) as "eth04",                   
    sum(CASE WHEN eth01 is not null then 1 ELSE 0 END) 
        + sum(CASE WHEN eth02 is not null then 1 ELSE 0 END) 
        + sum(CASE WHEN eth03 is not null then 1 ELSE 0 END) 
        + sum(CASE WHEN eth04 is not null then 1 ELSE 0 END) as "total"               
    from t
    where(eth01 = 'data' or eth02 = 'data' or eth03 = 'data'  or eth04 = 'data')               
    GROUP BY  device
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.