SQLize Online / PHPize Online  /  SQLtest Online

create table stores(store_id INT, city_desc VARCHAR(50), district VARCHAR (50) ); INSERT INTO stores(store_id, city_desc, district) VALUES(10, 'Moscow', 'Moscow'), (11, 'Moscow', 'Moscow'), (31, 'Ekaterinburg', 'Ural'), (21, 'NN','NN'); create table product( cma VARCHAR(50), art_no INT, art_name VARCHAR(100)); INSERT INTO product(cma, art_no, art_name) VALUES('Fruits', 456131,'ПЕТРУШКА КУДРЯВАЯ'), ('Wines', 432456, 'КОМПОЗИЦИЯ В КЕРАМИКЕ D-15'), ('Grocery', 50240, '460Г СЕЛЬДЬ Ф/К С ДЫМКОМ МАТЬЕ'), ('Grocery', 363636, '460Г СЕЛЬДЬ Ф/К С ДЫМКОМ МАТЬЕ'), ('Fruits', 482308, '460Г СЕЛЬДЬ Ф/К С ДЫМКОМ МАТЬЕ'), ('Fruits', 52300, '460Г СЕЛЬДЬ Ф/К С ДЫМКОМ МАТЬЕ'), ('Sweets', 50260, '500Г ХЛОПЬЯ ГРЕЧНЕВЫЕ GARNISH'), ('Sweets', 50280, '500Г ХЛОПЬЯ ГРЕЧНЕВЫЕ GARNISH'), ('Wines', 50360, '500Г ХЛОПЬЯ ГРЕЧНЕВЫЕ GARNISH'); create table sales(store_id INT, date_of_day DATE, art_no INT, sell_qty_colli INT, Sell_price float ); INSERT INTO sales(store_id, date_of_day, art_no, sell_qty_colli, Sell_price) VALUES(21, '2018-12-22', 456131, 1, 95.2), (21, '2018-12-22', 50240, 1, 253.39), (10, '2018-1-2', 50360, 4, 300), (10, '2018-1-2', 432456, 4, 97.82), (31, '2018-1-2', 50240, 4, 50), (31, '2018-1-2', 50260, 4, 100), (31, '2018-1-2', 50280, 4, 120), (31, '2018-1-2', 363636, 4, 100), (11, '2019-11-6', 432456, 1, 20.0) ; create table prod_disc( date_from DATE, date_to DATE, art_no INT, store_id INT, base_price float, discount_price float); INSERT INTO prod_disc(date_from, date_to, art_no, store_id, base_price, discount_price) VALUES ('2018-12-5','2019-12-5', 453161, 356, 95.2, 83.31), ('2019-11-6', '2019-11-7', 484955, 310, 599.7, 541.7), ('2017-1-17','2017-2-1', 50360, 29, 300, 260), ('2020-6-30', '2020-8-12', 432456, 28, 18.888, 11.39), ('2017-8-31','2017-10-31', 341832, 23, 1736.0042, 1389), ('2019-11-6', '2019-12-6', 50240, 11, 50, 20), ('2019-11-6', '2019-12-6', 50260, 11, 100, 80), ('2019-11-6', '2019-12-6', 50280, 11, 120, 100), ('2019-11-6', '2019-12-6', 363636, 11, 100, 90); select * from stores; select * from product; select * from sales; select * from prod_disc; SELECT s.store_id, cma, art_name, round(avg(Sell_price),2) as cредняя_цена_продажи FROM sales s LEFT JOIN product pr ON s.art_no = pr.art_no LEFT JOIN stores st ON s.store_id=st.store_id LEFT JOIN prod_disc pr_d on s.art_no=pr_d.art_no WHERE st.city_desc='Ekaterinburg' and cma='Grocery' or cma='Sweets' GROUP BY s.store_id, cma, art_name union SELECT s.store_id, cma, art_name, round(avg(Sell_price),2) as cредняя_цена_продажи FROM sales s LEFT JOIN product pr ON s.art_no = pr.art_no LEFT JOIN stores st ON s.store_id=st.store_id LEFT JOIN prod_disc pr_d on s.art_no=pr_d.art_no WHERE s.store_id = '11' and cma='Wines' GROUP BY s.store_id, cma, art_name ; SELECT AVG(Sell_price) AS average_price FROM sales WHERE store_id = 10 AND LEFT JOIN product ON sales.art_no = product.art_no WHERE cma='Wines'