I have oracle database table 'mytable' with following data-
Sr_NO Name Status Date
121 HP OK 12/06/2018
122 Dell OK 15/06/2018
123 MAC NOK 30/07/2018
124 Apple NOK 03/09/2018
125 MI NOK 04/09/2018
126 Oppo NOK 05/09/2018
127 Vivo OK 06/09/2018
I want to get output in following format-
Category Count OK NOK
Till 30th Jul 3 2 1
After 30th Jul 4 1 3
Total 7 3 4
You can use conditional aggregation to get the columns and grouping sets to get the additional row:
select (case when date <= '2018-07-30' then 'Till 30th Jul'
else 'After 30th Jul'
end) as category,
count(*),
sum(case when status = 'OK' then 1 else 0 end) as OK,
sum(case when status = 'NOK' then 1 else 0 end) as NOK
from t
group by grouping sets ( ( (case when date <= '2018-07-30' then 'Till 30th Jul'
else 'After 30th Jul'
end)
),
()
);
You can add total as a category -- in your case -- using a comparison to NULL:
select (case when date is null then 'Total'
when date <= '2018-07-30' then 'Till 30th Jul'
else 'After 30th Jul'
end) as category,
You have no NULL dates in your data, so this is fine. Otherwise you should use GROUPING().
Don't use Oracle keywords like DATE and COUNT as column names. I changed those to DT and CT.
This is a simple job for GROUP BY ROLLUP, after you create an additional column FLAG to show which group each row belongs to. I created the flag as 'b' for "before July 30 2018" - otherwise the flag is NULL. (This allows me to use the flag in ORDER BY also, since by default ordering by FLAG is NULLS LAST.)
Remember to remove the WITH clause in its entirety before you run the query, and use your actual table and column names.
alter session set nls_date_format = 'dd/mm/yyyy';
with
test_data (sr_no, name, status, dt) as (
select 121, 'HP' , 'OK' , to_date('12/06/2018') from dual union all
select 122, 'Dell' , 'OK' , to_date('15/06/2018') from dual union all
select 123, 'MAC' , 'NOK', to_date('30/07/2018') from dual union all
select 124, 'Apple', 'NOK', to_date('03/09/2018') from dual union all
select 125, 'MI' , 'NOK', to_date('04/09/2018') from dual union all
select 126, 'Oppo' , 'NOK', to_date('05/09/2018') from dual union all
select 127, 'Vivo' , 'OK' , to_date('06/09/2018') from dual
)
select case grouping_id(flag) when 0
then case flag when 'b' then 'Till 30th July'
else 'After 30th July'
end
else 'Total'
end as category
, count(status) as ct
, count(case status when 'OK' then 0 end) as ok
, count(case status when 'NOK' then 0 end) as nok
from ( select sr_no, name, status, dt,
case when dt <= date '2018-07-30' then 'b' end as flag
from test_data
)
group by rollup(flag)
order by grouping_id(flag), flag
;
CATEGORY CT OK NOK
--------------- ---------- ---------- ----------
Till 30th July 3 2 1
After 30th July 4 1 3
Total 7 3 4
Categoryalso show which year that "30th July" is?