new sample suggestion: sales vs costs

[toniyecla]

A quick first idea, you can certainly improve it...

SELECT data_table.year AS Año, data_table.month AS Mes, data_table.value AS Marca, TRUNCATE(SUM(data_table.amount), 2) AS Ventas, TRUNCATE(SUM(CPED.value), 2) AS Coste FROM (SELECT SFOI.product_id, EAOV.value, (SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded) AS amount, DATE_FORMAT(SFOI.created_at, '%Y') AS year, DATE_FORMAT(SFOI.created_at, '%m (%M)') AS month FROM eav_attribute_option AS EAO INNER JOIN eav_attribute_option_value AS EAOV ON EAO.option_id = EAOV.option_id AND EAO.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'manufacturer') INNER JOIN catalog_product_entity_int AS CPEI ON CPEI.value = EAOV.option_id INNER JOIN sales_flat_order_item AS SFOI ON CPEI.entity_id = SFOI.product_id AND SFOI.price > 0 -- AND SFOI.product_id = 2409 -- AND SFOI.created_at >= '2016-01-01' AND SFOI.created_at < '2016-12-31' ) AS data_table JOIN catalog_product_entity_decimal as CPED ON CPED.entity_id = data_table.product_id AND CPED.value > 0 AND CPED.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'cost') GROUP BY data_table.year, data_table.month, data_table.value ORDER BY data_table.year ASC, data_table.month ASC, data_table.value ASC

{"filterable": {"Año":"adminhtml/widget_grid_column_filter_text", "Mes":"adminhtml/widget_grid_column_filter_text", "Marca":"adminhtml/widget_grid_column_filter_text", "Ventas":"adminhtml/widget_grid_column_filter_range", "Coste":"adminhtml/widget_grid_column_filter_range"}}