Best oracle questions in February 2012

Tricky GROUP BY issue on ORACLE

Asked on Thu, 16 Feb 2012 by Ajantis sql oracle
6 votes

I’m currently facing an issue that my Oracle knowledge cannot solve, I’m definitely not DB expert and that’s why I ask you if you have any idea how to solve my SQL query issue.

Here’s my problem, I have two tables, let’s call them DEVICE_TABLE and COUNT_TABLE

COUNT_TABLE looks like :

    DEVICE (Int) PK         |       QUANTITY (Int)
- - - - - - - - - - - - - - - - - - - - - - - - - - -
        1001                |              4
- - - - - - - - - - - - - - - - - - - - - - - - - - -
        1002                |             20
- - - - - - - - - - - - - - - - - - - - - - - - - - - 
        1003                |              1
…

DEVICE_TABLE looks like :

     ID (Int) PK            |      WiFi (String)            |     Email (String)          |   Bluetooth(String)           |   …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        1001                |             Yes               |               No            |                 No            |   …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        1002                |             Yes               |               Yes           |                 No            |   …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        1003                |             Unknown           |               Unknown       |                 Yes           |   …
…

Constraints are :

DEVICE_TABLE.ID = COUNT_TABLE.DEVICE

WiFi, Email, Bluetooth… are Strings that can only be : “Yes”, “No” or “Unknown”

Finally, my SQL request result expected is (based on my example):

         Feature        |            Yes           |              No            |            Unknown          
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
        WiFi            |             24           |                 0          |                 1                  
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
       Email            |             20           |                 4          |                 1                  
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
    Bluetooth           |              1           |                24          |                 0                   
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
…

In few words, aim of this request is to sum all devices count that are compatible with a particular feature.

Thank you in advance if you have any clue on how to achieve this ! (Maybe it is not possible…)

Answered by René Nyffenegger

In Oracle 11, you can use the pivot clause together with the unpivot clause:

with 
count_table as (
     select 1001 device_id,  4 quantity from dual union all
     select 1002 device_id, 20 quantity from dual union all
     select 1003 device_id,  1 quantity from dual 
),
device_table as (
     select 1001 id, 'Yes'     wifi, 'No'       email, 'No'  bluetooth from dual union all
     select 1002 id, 'Yes'     wifi, 'Yes'      email, 'No'  bluetooth from dual union all
     select 1003 id, 'Unknown' wifi, 'Unknown'  email, 'Yes' bluetooth from dual 
)
----------------------------------------
select * from (
      select
        feature,
        yes_no_unknown,
        sum(quantity)  quantity
      from 
         count_table  c join 
         device_table d on c.device_id = d.id
      unpivot  ( yes_no_unknown
                 for feature in (wifi, email, bluetooth)
      ) 
      group by 
      feature,
      yes_no_unknown
)  
pivot ( sum (quantity)
        for yes_no_unknown in ('Yes' as yes, 'No' as no, 'Unknown' as unknown)
)
;

Alternatively, you might want to join the two existing tables to a third table that containts the values for the three desired rows. It's probably a bit easier to read, too:

with 
count_table as (
     select 1001 device_id,  4 quantity from dual union all
     select 1002 device_id, 20 quantity from dual union all
     select 1003 device_id,  1 quantity from dual 
),
device_table as (
     select 1001 id, 'Yes'     wifi, 'No'       email, 'No'  bluetooth from dual union all
     select 1002 id, 'Yes'     wifi, 'Yes'      email, 'No'  bluetooth from dual union all
     select 1003 id, 'Unknown' wifi, 'Unknown'  email, 'Yes' bluetooth from dual 
)
----------------------------------------
select
   f.txt,
   sum(case when ( f.txt = 'wifi'      and d.wifi      = 'Yes' ) or
                 ( f.txt = 'email'     and d.email     = 'Yes' ) or
                 ( f.txt = 'bluetooth' and d.bluetooth = 'Yes' ) 
            then   c.quantity
            else   0 end
      ) yes,
   sum(case when ( f.txt = 'wifi'      and d.wifi      = 'No' ) or
                 ( f.txt = 'email'     and d.email     = 'No' ) or
                 ( f.txt = 'bluetooth' and d.bluetooth = 'No' ) 
            then   c.quantity
            else   0 end
      ) no,
   sum(case when ( f.txt = 'wifi'      and d.wifi      = 'Unknown' ) or
                 ( f.txt = 'email'     and d.email     = 'Unknown' ) or
                 ( f.txt = 'bluetooth' and d.bluetooth = 'Unknown' ) 
            then   c.quantity
            else   0 end
      ) unknown
from 
   count_table  c                                   join 
   device_table d on c.device_id = d.id     cross   join
   (
        select 'wifi'      txt from dual union all
        select 'email'     txt from dual union all
        select 'bluetooth' txt from dual
   ) f
group by 
    f.txt;