Magento MySQL – The most popular sold product per day

I would like to create “The most popular sold product per day” report  for the last 30 days. The header of the report should look like the picture below. We need the Date, Product SKU number, Product name, Product Brand and Quantity Sold. The most popular product is the product sold the maximum number of times on that day.


SELECT CreatedAt, sku AS SKU, name AS Description,
 eaov.value AS Brand, MAX( qty_total ) AS Qty

SELECT DATE( created_at ) AS CreatedAt, sku,
 SUM( qty_ordered ) AS qty_total, name, product_id
FROM `sales_flat_order_item` AS sfoi
WHERE sfoi.created_at > DATE_SUB( CURDATE( ) , INTERVAL 360 DAY )
GROUP BY sku, CreatedAt
ORDER BY qty_total DESC
) AS item_count
/* get brand name*/
LEFT JOIN `catalog_product_entity_int` AS cpei ON cpei.attribute_id =81
 AND cpei.entity_id = item_count.product_id
LEFT JOIN `eav_attribute_option_value` AS eaov ON eaov.option_id = cpei.value
GROUP BY CreatedAt

Disadvantage: if two product are equally popular, only one of them is chosen for the report.

Place the results of a MySQL query into a CSV file

The results of a MySQL query can be placed into a CSV file using the following query:

INTO OUTFILE 'file_path'

To include header write another query before the “main” query. It returns the header, and the “main” query returns the data. Union joins them together:

… Your MySQL query here;

View on Github

Magento MySQL – CURDATE() vs NOW()

The CURDATE() function returns the current date.
The NOW() function returns the current system date and time.
If you want to get a sales report for the last X days, make sure to think first what kind of output you want to get.
For me, if I run the same sales report for the last X days in the morning and in the evening of the same day, I want to get the same report without regard the current time, so I will use the CURDATE() function.
In case if we use the NOW() function, and two people want to generate the same report and then do something with it, each of them will get a slightly different version of the report since it’s nearly impossible to run the SQL query at the same time by two different people. It will create discrepancies.
Example: Orders report for the last X days

SELECT SFO.increment_id AS OrderId, SFO.base_subtotal AS BasketAmount, 
	SFO.base_shipping_amount AS Shipping, 	
    SFO.base_discount_amount AS Discount, 
	SFO.base_tax_amount AS Tax, 
    IFNULL( SFO.base_total_paid, 0 ) AS CustomerPaid
FROM  `sales_flat_order` AS SFO
	SFO.created_at > DATE_SUB( CURDATE( ) , INTERVAL X DAY ) 
ORDER BY SFO.created_at DESC  

View on Github

Magento MySQL – Calculate average number of orders per day and per month

What is the average number of orders per day?

SELECT AVG( orders_num )
SELECT created_at, COUNT( DISTINCT order_id ) orders_num
FROM `sales_flat_order_item`
GROUP BY CAST( created_at AS DATE )

What is the average number of orders per month?

SELECT AVG( orders_num ) 
SELECT created_at, COUNT( DISTINCT order_id ) orders_num
FROM  `sales_flat_order_item` 
GROUP BY MONTH( created_at )

View on Github