By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE articles
(art varchar2(4), price decimal(12,2))
;
INSERT ALL
INTO articles (art, price)
VALUES ('1toy', 2.00)
INTO articles (art, price)
VALUES ('2toy', 2.50)
INTO articles (art, price)
VALUES ('3toy', 1.50)
INTO articles (art, price)
VALUES ('4toy', 6.00)
INTO articles (art, price)
VALUES ('1toy', 2.50)
INTO articles (art, price)
VALUES ('1toy', 3.00)
SELECT * FROM dual
;
6 rows affected
CREATE TABLE checks
(checks varchar2(6), art varchar2(4), quantity int)
;
INSERT ALL
INTO checks (checks, art, quantity)
VALUES ('1check', '1toy', 2)
INTO checks (checks, art, quantity)
VALUES ('1check', '1toy', 5)
INTO checks (checks, art, quantity)
VALUES ('1check', '1toy', 1)
INTO checks (checks, art, quantity)
VALUES ('1check', '2toy', 1)
INTO checks (checks, art, quantity)
VALUES ('1check', '4toy', 3)
INTO checks (checks, art, quantity)
VALUES ('2check', '2toy', 1)
INTO checks (checks, art, quantity)
VALUES ('2check', '1toy', 2)
SELECT * FROM dual
;
7 rows affected
with a as (
select art
,price
from
(
select a.*
,dense_rank() over(partition by art order by price) as dns_rnk
,count(*) over(partition by art) as cnt
from articles a
) a
where cnt > 1 and dns_rnk = 2
or cnt = 1
)
select art
,sum(quantity)*price as total
from a left join checks c using(art)
group by art, price, checks
having checks = '1check'
order by art
| ART | TOTAL |
|---|---|
| 1toy | 20 |
| 2toy | 2.5 |
| 4toy | 18 |