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

CodePudding user response：

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.

CodePudding user response：

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;