SQL query to return records depending on the number of filled columns
I have a table of items. The item ID is stored in the column ITEM
. All elements have different variants: t21, t2, t2, t2. If the element has a special variant type, the column (named after variant type) is filled with a number.
I would like to select only items that have at least 4 options (4 column options are filled with a number).
ITEM 80 85 90 95 100 105 110 115
A 1 1 3 <-- 3 variants
B 2 1 3 <-- 3 variants
C 1 3 <-- 2 variants
D 1 3 <-- 2 variants
E 1 1 1 1 <-- 4 variants
In this example, Element E will be the only one selected as it has 4 options.
create table itemtest (
item varchar2(30 char),
"80" integer,
"85" integer,
"90" integer,
"95" integer,
"100" integer,
"105" integer,
"110" integer,
"115" integer
);
insert into itemtest values ('A', 1, null, null, null, 1, null, null, 3);
insert into itemtest values ('B', 2, null, null, null, 1, null, null, 3);
insert into itemtest values ('C', null, null, null, 1, null, null, null, 3);
insert into itemtest values ('D', null, 1, null, null, null, null, null, 3);
insert into itemtest values ('E', 1, null, null, 1, null, null, 1, 1);
commit;
source to share
Save yourself from typing tedious expressions CASE
. Use Oracle for the underrated NVL2()
for this!
select itemtest.*
from itemtest
where NVL2("80" , 1, 0) + NVL2("85" , 1, 0) +
NVL2("90" , 1, 0) + NVL2("95" , 1, 0) +
NVL2("100", 1, 0) + NVL2("105", 1, 0) +
NVL2("110", 1, 0) + NVL2("115", 1, 0) >= 4;
From the docs:
NVL2(expr1, expr2, expr3)
NVL2 allows you to determine the value returned by a query depending on whether the specified expression is null or not. If expr1 is nonzero, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.
Bonus. Impress (or annoy) your employees with a beautiful UNPIVOT offer
I don't know why I didn't think about it before. In fact, you don't really open your columns to count them. Here's how:
SELECT item, COUNT(*)
FROM itemtest
UNPIVOT (
variants FOR code IN ("80", "85", "90", "95", "100", "105", "110", "115")
)
GROUP BY item
HAVING COUNT(*) >= 4
ORDER BY item
This will give
ITEM COUNT(*)
--------------
E 4
How it works?
Here's a simple operator UNPIVOT
without grouping and aggregation:
SELECT *
FROM itemtest
UNPIVOT (
variants FOR code IN ("80", "85", "90", "95", "100", "105", "110", "115")
)
What gives...
ITEM CODE VARIANTS
--------------------
A 80 1
A 100 1
A 115 3
B 80 2
B 100 1
B 115 3
C 95 1
C 115 3
D 85 1
D 115 3
E 80 1
E 95 1
E 110 1
E 115 1
The remaining ( GROUP BY
and HAVING
) - it's just plain SQL.
source to share
Just count them and choose when is greater than or equal to 4
select *
from itemtest
where
CASE WHEN "80" IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN "85" IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN "90" IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN "95" IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN "100" IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN "105" IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN "110" IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN "115" IS NOT NULL THEN 1 ELSE 0 END >= 4
source to share
If you want to return rows based on the number of choices, you can use the CASE statement to check how many columns are full:
select
i.item
from
(
select
item,
case when "80" is not null then 1 else 0 end +
case when "85" is not null then 1 else 0 end +
case when "90" is not null then 1 else 0 end +
case when "95" is not null then 1 else 0 end +
case when "100" is not null then 1 else 0 end +
case when "105" is not null then 1 else 0 end +
case when "110" is not null then 1 else 0 end +
case when "115" is not null then 1 else 0 end num_variants
from
itemtest
) i
where
num_variants >= 4;
You're probably stuck with the design, but it would be much easier if the possible variations were held in a separate ITEM_VARIANT table:
ITEM VARIANT_CODE VARIANT_VALUE
---- ------------ -------------
A 80 1
A 100 1
A 115 3
B 80 2
...
...
...
E 115 1
Then you have a nice simple query - like this:
select
item
from
(
select
item,
count(*) num_variants
from
item_variant
group by
item
)
where
num_variants >= 4;
source to share