Reputation: 85
Tricky GROUP BY issue on ORACLE
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…)
Upvotes: 6
Views: 1297
Answers (4)
Reputation: 1
If you are working with Oracle 11g, Pivot feature can be used to get the solution.Please refer the below 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
Upvotes: 0
Reputation: 40553
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;
Upvotes: 3
Reputation: 18410
1) Data model could probably be improved, by creating a device capability table that would have device ID, references DEVICE_TABLE.ID and Capability.
Where you have Yes in the device table enter a row into device capability, and get rid of the feature/capability columns from device table.
Barring that:
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
Upvotes: 1
Reputation: 10517
I'm glad 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
...
Upvotes: 1