Oh snap! You are using an old version of browser. Update your browser to get new awesome features. Click for more details.

Magento direct sql query


Hello Friend's,

Here some usefully mysql query.

========================================================================

Number Orders - Sales - Avg Per Order

*********************************************************************************

AVG Order Value Per Day

SELECT DATE(`created_at`) AS 'Date', AVG(`grand_total`) AS 'AVG Order Value Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC

--------------------------------------------------------------------------------------

AVG Order Value Per Week

SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per Week' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC

--------------------------------------------------------------------------------------

AVG Order Value Per Month

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`), MONTH(`created_at`) 
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC

--------------------------------------------------------------------------------------

AVG Order Value Per year

SELECT YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per year' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Sale Per Day

SELECT DATE(`created_at`) AS 'Date',SUM(`grand_total`) AS 'Total Sale Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Sale Per Week

SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per Week' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC

--------------------------------------------------------------------------------------

Total sales per month

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`), MONTH(`created_at`) 
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Sale Per year

SELECT YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per year' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC

--------------------------------------------------------------------------------------

Specific Month of year  sale

EX. 
" MONTH(`created_at`) = '01' " = Number of month(05)
" YEAR(`created_at`) = '2016' " = Number of year(2013)

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total sale',`status` FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' AND MONTH(`created_at`) = '01' AND YEAR(`created_at`) = '2016'

--------------------------------------------------------------------------------------

Specific Month wise yearly sale

EX. 
" YEAR(`created_at`) = '2015' " = Number of year(2013)

SELECT MONTHNAME(`created_at`) AS 'Month', SUM(`grand_total`) AS 'Total Sale Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' AND YEAR(`created_at`) = '2015' GROUP BY MONTH(`created_at`) ORDER BY MONTH(`created_at`) ASC

--------------------------------------------------------------------------------------

Total Order Per Day

SELECT DATE(`created_at`) AS 'Date', COUNT(*) AS 'Total Order Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Order Per Week

SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Week' FROM `sales_flat_order` WHERE `store_id` = 2  GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Order Per Month

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY YEAR(`created_at`), MONTH(`created_at`) ORDER BY YEAR(`created_at`) DESC,MONTH(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Order Per Year

SELECT YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Year' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC

*******************************************

Sales by New Customers (How many new and sales)

Consider created customers in current month customers as new customer

SELECT `sales_flat_order`.`customer_email`,`sales_flat_order`.`customer_id`,`customer_entity`.`created_at` AS 'Customer Created Date',COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` JOIN `customer_entity` ON `customer_entity`.`entity_id` = `sales_flat_order`.`customer_id` WHERE MONTH(`customer_entity`.`created_at`) = MONTH(NOW()) AND YEAR(`customer_entity`.`created_at`) = YEAR(NOW()) AND YEAR(`sales_flat_order`.`created_at`) = YEAR(NOW()) AND `sales_flat_order`.`store_id` = '2' GROUP BY `sales_flat_order`.`customer_email`

*********************************************************************************

Sales by Existing Customers

Consider not created customers in current month as existing customer

SELECT `sales_flat_order`.`customer_email`,`sales_flat_order`.`customer_id`,`customer_entity`.`created_at` AS 'Customer Created Date',`sales_flat_order`.`created_at` AS 'Order Created Date',COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` JOIN `customer_entity` ON `customer_entity`.`entity_id` = `sales_flat_order`.`customer_id` WHERE MONTH(`customer_entity`.`created_at`) != MONTH(NOW()) AND YEAR(`customer_entity`.`created_at`) != YEAR(NOW()) AND MONTH(`sales_flat_order`.`created_at`) = MONTH(NOW()) AND YEAR(`sales_flat_order`.`created_at`) = YEAR(NOW()) AND `sales_flat_order`.`store_id` = '2' GROUP BY `sales_flat_order`.`customer_email`

*********************************************************************************

Sales by Customers By Date

SELECT `customer_firstname`,`customer_lastname`,`customer_email`,`customer_id`, COUNT(*) AS 'Total Order Per Customer', DATE(`created_at`) AS 'Date' FROM `sales_flat_order` where `store_id` = 2 GROUP BY DATE(`created_at`), `customer_email` ORDER BY DATE(`created_at`) DESC

*********************************************************************************

Sales By State

SELECT `sales_flat_order_address`.`region`,count(*) FROM `sales_flat_order` JOIN `sales_flat_order_address` ON `sales_flat_order`.`entity_id` = `sales_flat_order_address`.`parent_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_address`.`address_type` = 'shipping' GROUP BY `sales_flat_order_address`.`region`

*********************************************************************************

Sales By Product

--------------------------------------------------------------------------------------

Total Sale for all product

SELECT `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order`.`status` = 'complete' GROUP BY `sales_flat_order_item`.`sku`

--------------------------------------------------------------------------------------

Total Sale for specific Product

SELECT `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete'

--------------------------------------------------------------------------------------

Total Sale for specific Product with specific Day

SELECT DATE(`sales_flat_order`.`created_at`) AS 'Date', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND DATE(`sales_flat_order`.`created_at`) = '2015-12-15'

--------------------------------------------------------------------------------------

Total Sale for specific Product with specific month period

SELECT YEAR(`sales_flat_order`.`created_at`) AS 'Year', MONTHNAME(`sales_flat_order`.`created_at`) AS 'Month', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND MONTH(`sales_flat_order`.`created_at`) = '10' AND YEAR(`sales_flat_order`.`created_at`) = '2014'

--------------------------------------------------------------------------------------

Total Sale for specific Product with specific year period

SELECT YEAR(`sales_flat_order`.`created_at`) AS 'Year', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND YEAR(`sales_flat_order`.`created_at`) = '2015'

*********************************************************************************

Sales by type of Customer


SELECT `customer_group`.`customer_group_code` AS 'Customer Role', COUNT(`sales_flat_order`.`customer_id`) AS 'Total Customer' FROM `sales_flat_order` JOIN `customer_group` ON `customer_group`.`customer_group_id` = `sales_flat_order`.`customer_group_id` where `sales_flat_order`.`store_id` = 2 GROUP BY `customer_group`.`customer_group_code`

--------------------------------------------------------------------------------------

Sales by specific type of Customer
EX. - 'Wholesale'

SELECT `sales_flat_order`.`entity_id`, `sales_flat_order`.`customer_id`,`sales_flat_order`.`customer_email`,`customer_group`.`customer_group_code` FROM `sales_flat_order`
JOIN `customer_group` ON `customer_group`.`customer_group_id` = `sales_flat_order`.`customer_group_id`
where `sales_flat_order`.`store_id` = 2 AND `customer_group`.`customer_group_code` = 'Wholesale'

*********************************************************************************

Sales by customer on specific period (Report that show how many times during year)

SELECT YEAR(`created_at`) AS 'Year',`customer_firstname`,`customer_lastname`,`customer_email` AS 'Customer Email',`customer_id`,COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` WHERE `store_id` = 2 AND YEAR(`created_at`) = '2015' GROUP BY `customer_email` ORDER BY COUNT(`customer_email`) DESC

*********************************************************************************

Birthdays per month (List Customers)

SELECT `customer_entity`.`email`,`customer_entity_varchar`.`value` FROM `customer_entity` JOIN `customer_entity_varchar` ON `customer_entity`.`entity_id` = `customer_entity_varchar`.`entity_id` WHERE `customer_entity_varchar`.`attribute_id` = 11 AND MONTH(`customer_entity_varchar`.`value`) = MONTH(NOW())

*********************************************************************************

Number of orders per customers

SELECT `customer_email` AS 'Customer Email',COUNT(`customer_email`) AS 'Total Order By Customer' FROM `sales_flat_order` WHERE `store_id` = 2  GROUP BY `customer_email` ORDER BY COUNT(`customer_email`) DESC


*********************************************************************************

Show all customer attribute data from Magento database

SELECT ce.*, ea.attribute_code, 
    CASE ea.backend_type 
       WHEN 'varchar' THEN ce_varchar.value
       WHEN 'int' THEN ce_int.value
       WHEN 'text' THEN ce_text.value
       WHEN 'decimal' THEN ce_decimal.value
       WHEN 'datetime' THEN ce_datetime.value
       ELSE NULL
    END AS value
  FROM customer_entity AS ce 
  LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
  LEFT JOIN customer_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
  LEFT JOIN customer_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
  LEFT JOIN customer_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
  LEFT JOIN customer_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
  LEFT JOIN customer_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'


*********************************************************************************

Update status form "fulfillment" to "complete" in sales order table

SELECT * FROM `sales_flat_order` where `store_id` = 2 AND `state` = 'complete' AND `status` = 'fulfillment'

UPDATE `sales_flat_order` SET `status` = 'complete' where `store_id` = 2 AND `state` = 'complete' AND `status` = 'fulfillment'

----------------------------------------------------------------------------------------

Update status form "fulfillment" to "complete" in sales order grid table

SELECT sg.* FROM `sales_flat_order_grid` as sg JOIN `sales_flat_order` as s ON s.`entity_id` = sg.`entity_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment'

UPDATE `sales_flat_order_grid` as sg JOIN `sales_flat_order` as s ON s.`entity_id` = sg.`entity_id` SET sg.`status` = 'complete' WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment'

----------------------------------------------------------------------------------------

Delete status "fulfillment" form sales order status history table

SELECT sh.* FROM `sales_flat_order_status_history` as sh JOIN `sales_flat_order` as s ON s.`entity_id` = sh.`parent_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment' AND sh.`status` = 'fulfillment' AND sh.`entity_name` = 'order'

DELETE sh.* FROM `sales_flat_order_status_history` as sh JOIN `sales_flat_order` as s ON s.`entity_id` = sh.`parent_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment' AND sh.`status` = 'fulfillment' AND sh.`entity_name` = 'order'


*********************************************************************************

Update order table date (day light saving)

UPDATE `sales_flat_order` 
SET `created_at`= ADDDATE(`created_at`, INTERVAL 60 MINUTE) 
WHERE `store_id`= 3 AND (created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2012-11-04 02:00:00' AND '2013-03-10 02:00:00'
OR created_at BETWEEN '2013-11-03 02:00:00' AND '2014-03-09 02:00:00'
OR created_at BETWEEN '2014-11-02 02:00:00' AND '2015-03-08 02:00:00'
OR created_at BETWEEN '2015-11-01 02:00:00' AND '2016-03-13 02:00:00');

----------------------------------------------------------------------------------------

Update order grid table date (day light saving)

UPDATE `sales_flat_order_grid` 
SET `created_at`= ADDDATE(`created_at`, INTERVAL 60 MINUTE) 
WHERE `store_id`= 3 AND (created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2012-11-04 02:00:00' AND '2013-03-10 02:00:00'
OR created_at BETWEEN '2013-11-03 02:00:00' AND '2014-03-09 02:00:00'
OR created_at BETWEEN '2014-11-02 02:00:00' AND '2015-03-08 02:00:00'
OR created_at BETWEEN '2015-11-01 02:00:00' AND '2016-03-13 02:00:00');


*********************************************************************************

Get all refund

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`total_refunded`) AS 'Total Refund Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `total_refunded` != '' GROUP BY YEAR(`created_at`), MONTH(`created_at`) 
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC


1 comment