SET @date_A = '2019-09-01';
SET @date_B = '2019-09-30';

SELECT razdel, pa_id, sku, brand, mask_for_id, char_name, char_unit_name, char_value, ceil(avg(price_torg)) price, round(avg(out_price_for_1),2) avg_price,
count(distinct o_id) orders, sum(count) Qnt, round(sum(total_price),0) Revenue, round(sum(margin),0) Profit
FROM
(SELECT o.id o_id, op.id op_id, pa.id pa_id, ba.name_brands brand, dr.name_ru razdel,
pm.mask_for_id,
c.id char_id,
ct_name.text char_name,
ct_unit_name.text char_unit_name,
ct.text char_value,
pa.price_torg,
cht.text sku,
op.count,
op.out_price_for_1,
op.count*op.out_price_for_1 total_price,
IF(in_price_for_1 = 0, op.out_price_for_1*0.3*count, (op.out_price_for_1-op.in_price_for_1)*op.count) margin
FROM yii_doc.orders o
LEFT JOIN yii_doc.order_cars oc ON o.id=oc.order_id
LEFT JOIN yii_doc.order_product op ON oc.id=op.car_id
LEFT JOIN yii_doc.prices_all pa ON op.price_all_id=pa.id
LEFT JOIN yii_doc.brand_all ba ON pa.brand_id=ba.id
LEFT JOIN yii_doc.dok_ga ga ON pa.pa_ga_id=ga.GA_ID
LEFT JOIN yii_doc.dok_razdel dr on ga.parent_id=dr.id
LEFT JOIN yii_doc.dok_izdelie di on dr.parent_id=di.id
LEFT JOIN yii_doc.dok_podrubrica dp on di.parent_id=dp.id
LEFT JOIN yii_doc.dok_razdel_template drt ON dr.id = drt.razdel_id
LEFT JOIN yii_doc.char c ON drt.modifier = c.id
LEFT JOIN yii_doc.char_text ct_name ON c.cha_name_id = ct_name.id
LEFT JOIN yii_doc.char_text ct_unit_name ON c.cha_name_unit_id = ct_unit_name.id
LEFT JOIN yii_doc.char_art ca ON (pa.id=ca.ca_pa_id and drt.modifier=ca.ca_char_id)
LEFT JOIN yii_doc.char_text ct ON ca.ca_char_text_id=ct.id
LEFT JOIN yii_doc.pa_mask pm on pa.id=pm.id
left join yii_doc.char_art cha on pa.id=cha.ca_pa_id
LEFT JOIN yii_doc.char ch on cha.ca_char_id=ch.id
LEFT JOIN yii_doc.char_text cht on cha.ca_char_text_id=cht.id
where date(o.date_create) between @date_A AND @date_B
and o.order_status_id IN(5,14)
and o.domain_id IN(2,3,4)
and ga.parent_id NOT IN(521,562)
and op.active=1
and dr.active=1
and dp.parent_id=9
and ch.cha_type="t"
group by o_id, op_id, pa_id, ba.name_ru, razdel
order by o_id, op_id, pa_id) a
group by razdel, pa_id, brand, mask_for_id, char_name, char_unit_name
order by revenue DESC