Archive of posts filed under the SQL category.

Magento MySQL – The most popular sold product per day

Posted by on

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.
Header

 

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

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

Posted by on

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

INTO OUTFILE 'file_path'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

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:

SELECT 'COLUMN1_NAME', 'COLUMN2_NAME'
UNION 
… Your MySQL query here;

View on Github

Magento MySQL – CURDATE() vs NOW()

Posted by on

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
WHERE 
	SFO.created_at > DATE_SUB( CURDATE( ) , INTERVAL X DAY ) 
ORDER BY SFO.created_at DESC

View on Github

Magento SQL – Five most popular products in general

Posted by on 
SELECT sfoi.sku AS SKU, sum(qty_ordered) AS Qty , name AS Description
FROM  `sales_flat_order_item` AS sfoi
JOIN  `catalog_product_entity` AS cpe ON cpe.entity_id = sfoi.product_id
GROUP BY product_id
ORDER BY sum(qty_ordered) DESC
LIMIT 5

However this query does not address the situation when we have more than five most popular product with the same number of units sold. If we have two products that were sold equal number of times, ‘Top Product Number Five’ and ‘Top Product Number 6’, MySQL will return either the ‘Top Product Number Five’ or the ‘Top Product Number 6’ randomly for the fifth most popular product each time we run the query.
View on Github

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

Posted by on

What is the average number of orders per day?

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

What is the average number of orders per month?

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

View on Github