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:

  1. Such a great articles in my carrier, It's wonderful commands like easiest understand words of knowledge in information's.

    digital marketing course in chennai

    ReplyDelete