Question 113162 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get count orders from previous week and substract for this week sql

Hello I had an sql getting the number of orders this week. My sample query below:

SELECT products.name,
   (SELECT COUNT(orders.product_id)
   FROM orders
   WHERE orders.product_id = products.id 
   AND DATE(orders.order_date) >=  [from] AND DATE(orders.order_date) [to]) as total_orders
FROM products p;

Now I want also get the total orders from previous week then get total orders from last week substract the orders from this week.

Results would be this

Products Total Orders This Week Difference from last and current week
Scissors 6 3
Tags:

2

Answers


  1. 0

    you could do a function to get those totals. The function will receive
    date1 when starts that week, and date2 when finish that week

    DELIMITER //
CREATE FUNCTION TOTAL_ORDERS_FROM
(
    date1 DATE,
    date2 DATE
)
RETURNS INT
BEGIN
    DECLARE totals INT;
    
    SET totals = (
        SELECT COUNT(orders.product_id)
        FROM orders
        WHERE orders.product_id = products.id 
        AND orders.order_date BETWEEN date1 AND date2
    );
    
    
    RETURN s;
END; //
DELIMITER ;

    Then in your query you only call your function 2 times, the first to get current week orders and the second get the difference between current and last week.

    SELECT products.name,
    TOTAL_ORDERS_FROM('10/10/2022','16/10/2022') as 'Total Orders This Week',
    (TOTAL_ORDERS_FROM('10/10/2022','16/10/2022') - TOTAL_ORDERS_FROM('3/10/2022','9/10/2022'))as 'Difference from last and current week'
FROM products p;
    Login or Signup to reply.
  2. 0

    You may use conditional aggregation.

    A pattern:

    SELECT SUM(orders.date BETWEEN CURRENT_DATE - INTERVAL 6 DAY AND CURRENT_DATE) this_week,
       SUM(orders.date BETWEEN CURRENT_DATE - INTERVAL 13 DAY AND CURRENT_DATE - INTERVAL 7 DAY) prev_week
FROM orders
WHERE orders.date BETWEEN CURRENT_DATE - INTERVAL 13 DAY AND CURRENT_DATE

    The query uses implicit aggregation (there is no GROUP BY – so the whole source rowset is treated as one group).

    The conditions used returns either 0 or 1 depends on the checking result, so SUM() acts as counting the amount of TRUE checks.

    Additional WHERE excludes the rows which will produce FALSE for all conditions, and eliminates excess checkings.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search