Notes

SELECT pc.name category_name, b.name name_brand, p.name model, ict.code icrt_code, sc.name, qt1.qty_shops_on, qt1.qty_shops_off, qt1.qty_shops qty_shops_in_chain, qt2.qty_shops_on, qt2.qty_shops_off, qt2.qty_shops,
qt3.price, qt3.qty, max(pp.expire_date) max_expired_date, count(pp.id) qty_prices, min(pp.price) min_price, max(pp.price) max_price, avg(pp.price) avg_price, stdev(pp.price) std_price, pp.product_id product_id, sc.id shop_chain_id, pc.id category_id
FROM price_publishes pp
JOIN product_categories pc on pc.id = pp.product_category_id
JOIN products p on p.id = pp.product_id
LEFT JOIN brands b on b.id = p.brand_id
LEFT JOIN icrtcodes ict on ict.product_id = p.id
JOIN shops s on s.id = pp.shop_id
JOIN shop_chains sc on sc.id = s.shop_chain_id
JOIN (SELECT shop_chain_id, COUNT(*) qty_shops, SUM(CASE WHEN shop_channel = 1 THEN 1 ELSE 0 END) qty_shops_on, SUM(CASE WHEN shop_channel = 2 THEN 1 ELSE 0 end) qty_shops_off
FROM shops where active = 1
GROUP BY shop_chain_id) qt1 on qt1.shop_chain_id = sc.id
JOIN (SELECT a.shop_chain_id, b.product_id, COUNT(DISTINCT(a.id)) qty_shops, SUM(CASE WHEN a.shop_channel = 1 THEN 1 ELSE 0 END) qty_shops_on, SUM(CASE WHEN a.shop_channel = 2 THEN 1 ELSE 0 END) qty_shops_off
FROM shops a, price_publishes b WHERE a.active = 1 AND a.id = b.shop_id
GROUP BY a.shop_chain_id, b.product_id) qt2 on qt2.shop_chain_id = sc.id AND qt2.product_id = pp.product_id
join (SELECT z.shop_chain_id, z.product_id, min(z.price) price, z.qty
FROM ( SELECT x.shop_chain_id, x.product_id, MAX(x.qty) qty
FROM ( SELECT s1.shop_chain_id, pp1.product_id, pp1.price, COUNT(DISTINCT(pp1.shop_id)) qty
FROM price_publishes pp1
JOIN shops s1 ON s1.id = pp1.shop_id
WHERE s1.shop_chain_id IS NOT NULL AND pp1.price > 0
GROUP BY s1.shop_chain_id, pp1.product_id, pp1.price) x
GROUP BY x.shop_chain_id, x.product_id ) y
JOIN ( SELECT x.shop_chain_id, x.product_id, x.price, MAX(x.qty) qty
FROM ( SELECT s1.shop_chain_id, pp1.product_id, pp1.price, COUNT(DISTINCT(pp1.shop_id)) qty
FROM price_publishes pp1
JOIN shops s1 ON s1.id = pp1.shop_id
WHERE s1.shop_chain_id IS NOT NULL AND pp1.price > 0
GROUP BY s1.shop_chain_id, pp1.product_id, pp1.price) x
GROUP BY x.shop_chain_id, x.product_id, x.price ) z
ON z.shop_chain_id = y.shop_chain_id AND z.product_id = y.product_id AND z.qty = y.qty
group by z.shop_chain_id, z.product_id, z.qty ) qt3 on qt3.shop_chain_id = sc.id AND qt3.product_id = pp.product_id
WHERE pp.country_id = 1 AND pp.price > 0 AND pp.product_category_id = 2 and pp.product_id in (1165)
GROUP BY pc.name, b.name, p.name, ict.code, sc.name, qt1.qty_shops_on, qt1.qty_shops_off, qt1.qty_shops, qt2.qty_shops_on, qt2.qty_shops_off, qt2.qty_shops, qt3.price, qt3.qty, pp.product_id, sc.id, pc.id