3

I am about to create an admin grid, which shows the report like >

  • take every single order from the beginning of the site
  • for every month we want to know how many total orders that were in the following values: values

and I assume the report like

grid result should like this

  • Any help will be appreciated :) – Radhu Apr 18 '18 at 9:44
1

Hi everyone, after a long time research i just found a query to build up the result. I am posting the sql query here.

SELECT sales_flat_order.entity_id AS entity_id, 
   CASE 
       WHEN sales_flat_order.`grand_total` BETWEEN '0' AND '100.99' THEN '0-100.99' 
       WHEN sales_flat_order.`grand_total` BETWEEN '101' AND '150.99' THEN '101-150.99' 
       WHEN sales_flat_order.`grand_total` BETWEEN '151' AND '200.99' THEN '151-200.99' 
       WHEN sales_flat_order.`grand_total` BETWEEN '201' AND '250.99' THEN '201-250.99' 
       WHEN sales_flat_order.`grand_total` BETWEEN '251' AND '300.99' THEN '251-300.99' 
       WHEN sales_flat_order.`grand_total` BETWEEN '301' AND '350.99' THEN '301-350.99' 
       WHEN sales_flat_order.`grand_total` BETWEEN '351' AND '400.99' THEN '351-400.99' 
       WHEN sales_flat_order.`grand_total` BETWEEN '401' AND '450.99' THEN '401-450.99' 
       WHEN sales_flat_order.`grand_total` BETWEEN '451' AND '500.99' THEN '451-500.99' 
       ELSE '501+' 
   END AS 'order_range', 
   DATE_FORMAT(sales_flat_order.created_at, '%Y %b') AS 'Month', 
   CONCAT('$', FORMAT(SUM(sales_flat_order.`grand_total`), 2)) AS 'Sales', 
   sales_flat_order.`grand_total` AS Amount, 
   COUNT(sales_flat_order.entity_id) AS 'Orders' 
FROM sales_flat_order 
GROUP BY MONTH,
     order_range 
ORDER BY DATE_FORMAT(sales_flat_order.created_at, '%Y %m'),
     CAST(SUBSTRING_INDEX(order_range, '-', 1) AS UNSIGNED)"; 

Once again thank you for the leads.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.