SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE checks ( id_check NUMBER NOT NULL, id_pos NUMBER NOT NULL, art VARCHAR2(20) NOT NULL, quantity NUMBER NOT NULL ); CREATE TABLE cat ( art VARCHAR2(100) NOT NULL, product NVARCHAR2(100) NOT NULL, category NVARCHAR2(100) NOT NULL ); -- insert INSERT INTO checks VALUES (1, 1, 'A1', 1); INSERT INTO checks VALUES (1, 2, 'A4', 3); INSERT INTO checks VALUES (1, 3, 'A2', 2); INSERT INTO checks VALUES (1, 4, 'A10', 1); INSERT INTO checks VALUES (1, 5, 'A8', 4); INSERT INTO checks VALUES (2, 1, 'A5', 1); INSERT INTO checks VALUES (2, 2, 'A7', 1); INSERT INTO checks VALUES (2, 3, 'A3', 2); INSERT INTO checks VALUES (3, 1, 'A2', 1); INSERT INTO checks VALUES (3, 2, 'A9', 1); INSERT INTO checks VALUES (3, 3, 'A5', 2); INSERT INTO checks VALUES (3, 4, 'A6', 1); INSERT INTO checks VALUES (3, 5, 'A3', 3); INSERT INTO checks VALUES (4, 1, 'A1', 3); INSERT INTO checks VALUES (4, 2, 'A2', 1); INSERT INTO checks VALUES (5, 1, 'A2', 1); INSERT INTO checks VALUES (5, 2, 'A8', 5); INSERT INTO checks VALUES (5, 3, 'A3', 1); INSERT INTO checks VALUES (5, 4, 'A4', 1); INSERT INTO checks VALUES (5, 5, 'A5', 1); INSERT INTO cat VALUES ('A1', 'кроссовки', 'обувь'); INSERT INTO cat VALUES ('A2', 'кеды', 'обувь'); INSERT INTO cat VALUES ('A3', 'сланцы', 'обувь'); INSERT INTO cat VALUES ('A4', 'куртка', 'одежда'); INSERT INTO cat VALUES ('A5', 'ветровка', 'одежда'); INSERT INTO cat VALUES ('A6', 'шорты', 'одежда'); INSERT INTO cat VALUES ('A7', 'палатка', 'туризм'); INSERT INTO cat VALUES ('A8', 'коврик', 'туризм'); INSERT INTO cat VALUES ('A9', 'спальник', 'туризм'); INSERT INTO cat VALUES ('A10', 'рюкзак', 'туризм'); select id_check -- , c.art -- , quantity -- , category -- , sum(quantity) as ssm , sum(case when category = 'обувь' then quantity end) as obuv , sum(case when category = 'туризм' then coalesce(quantity, 0) end) as turism from checks c inner join cat on c.art = cat.art and category in ('обувь', 'туризм') group by id_check