CODEWITHSUNDEEP
sqldatabaseoracle-database
Praveen Verma
Praveen Verma

Reputation: 41

Oracle SQL query to get output in specific format

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

Upvotes: 0

Views: 127

Answers (2)

user5683823
user5683823

Reputation:

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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().

Upvotes: 2

Related Questions