Menu
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…)
210
DISTINCT implies you want a distinct set of columns. However, GROUP BY implies you want to compute some sort of aggregate value which you are not. It will take more time in your case.
We can use GROUP BY without specifying any aggregate functions in the SELECT list. However, the same result is usually produced by specifying DISTINCT instead of using GROUP BY. According to Tom Kyte the two approaches are effectively equivalent (see AskTom "DISTINCT VS, GROUP BY").
In this SQL tutorial, we will learn how to use Order by and Group By in SQL. Group By in SQL is used to arrange similar data into groups and Order By in SQL is used to sort the data in ascending or descending order.
ORA-00979 “ Not a GROUP BY expression ” is an error issued by the Oracle database when the SELECT statement contains a column that is neither listed in GROUP BY nor aggregated. This error message can be confusing to beginners. Practice your SQL basics with 88 exercises in our interactive SQL Practice Set course!
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;
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
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
