SELECT sc.agency_contract -- , c.is_agent 
, SUM(opn.in_stock * pp.products_purchase_price)
FROM orders_products_new AS opn
JOIN categories AS c ON c.categories_id = opn.products_categories_id
JOIN products_prices AS pp ON pp.products_attributes_id = opn.products_attributes_id
JOIN orders AS o ON o.orders_id= opn.orders_id
LEFT JOIN suppliers_contracts AS sc ON sc.suppliers_id = c.suppliers_contracts_id
WHERE opn.is_deleted = 0 AND c.date_end BETWEEN '2015-10-01' AND '2015-10-31 23:59:29'

-- AND sc.agency_contract = 'Y' 

-- and c.is_agent = 1
AND o.orders_status = 6 AND opn.is_return = 0
group by sc.agency_contract
-- , c.is_agent