All geek questions in one place

Tricky GROUP BY on ORACLE

I am currently facing a problem that my Oracle knowledge cannot solve, I am definitely not a database expert, and this is why I ask you if you have an idea to solve my SQL query problem.

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

COUNT_TABLE looks like this:

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

DEVICE_TABLE looks like this:

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

Limitations:

DEVICE_TABLE.ID = COUNT_TABLE.DEVICE

WiFi, email, Bluetooth ... are strings that can only be Yes, No, or Unknown.

Finally, the expected result of the SQL query (based on my example):

  Feature |  Yes |  No |  Unknown          
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
         Wifi |  24 |  0 |  1                  
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
        Email |  20 |  4 |  1                  
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
     Bluetooth |  1 |  24 |  0                   
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
 ...

In a few words, the purpose of this query is to summarize the total number of devices compatible with a particular function.

Thanks in advance if you have any tips on how to achieve this! (Perhaps this is not possible ...)

+6
source share
4 answers

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

 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 can join two existing tables to a third table containing values ​​for the three desired rows. This is probably a little easier to read:

 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;
+3
source

I am pleased to please you - your db design is far from perfect in terms of relative db. The only possible way is to use UNION:

 select 'WiFi' as Feature, (select count(*) from DEVICE_TABLE where WiFi = 'Yes') as Yes, (select count(*) from DEVICE_TABLE where WiFi = 'No') as No union select 'Email' as Feature, (select count(*) from DEVICE_TABLE where Email = 'Yes') as Yes, (select count(*) from DEVICE_TABLE where Email = 'No') as No ...
+1
source

1) Perhaps the data model could be improved by creating a table of capabilities of the device, which would have a device identifier that refers to DEVICE_TABLE.ID and ability.

If you have Yes in the device table, enter the line in the device capability and get rid of the function / capability columns from the device table.

Ban:

 with Capabilities as ( select ID, 'WiFi' as capability, Wifi as has_capability from device_table union all select ID, 'Email', Email from device_table union all select ID, 'BlueTooth', BlueTooth from device_table ) select C.capability , sum(case when C.has_capability = 'Yes' then CNT.quantity end) as Yes , sum(case when C.has_capability = 'No' then CNT.quantity end) as No from device_table D inner join Capabilities C on C.ID = D.ID left outer join count_table CNT on CNT.DEVICE = D.ID group by C.capability order by C.capability
+1
source

If you are working with Oracle 11g, the Pivot function can be used to get the solution. Please refer to the following query:

 select features,nvl(yes,0) yes,nvl(no,0) no,nvl(unknown,0) unknown from ( select * from (select 'Wifi' as features,wifi,nvl(quantity,0) quantity from count_table, device_table where id = device_id) pivot (sum(nvl(quantity,0)) for Wifi in ('Yes' as yes,'No' as no,'Unknown' as unknown)) Union all select * from (select 'Bluetooth' as features,bluetooth,nvl(quantity,0) quantity from count_table, device_table where id = device_id) pivot (sum(nvl(quantity,0)) for bluetooth in ('Yes' as yes,'No' as no,'Unknown' as unknown)) union all select * from (select 'Email' as features,Email,nvl(quantity,0) quantity from count_table, device_table where id = device_id) pivot (sum(nvl(quantity,0)) for Email in ('Yes' as yes,'No' as no,'Unknown' as unknown)) ) order by yes desc

Refer to SQLFiddle here: http://sqlfiddle.com/#!4/97793/1/0

0
source

Source: https://habr.com/ru/post/908594/

More articles:


All Articles