how to get the weeks b/w two dates as week1,week2,week3 in mysql

Question

SELECT COUNT(order_id) AS xAxis, WEEK(created_at) AS yAxis FROM orders WHERE created_at >= startDate AND created_at <= endDate GROUP BY WEEK(created_at);

Answer 1

Try just using MIN and MAX:

SELECT
    COUNT(order_id) AS xAxis,
    WEEK(created_at) AS yAxis,
    MIN(created_at) AS week_start,
    MAX(created_at) AS week_end
FROM orders
WHERE
    created_at BETWEEN startDate AND endDate
GROUP BY
    WEEK(created_at);

The reasoning here is simple: the start of each week group of records should be the smallest date and vice-versa for the max.

Should your data not cover every day in the range, then you can join to a calendar table to bring in the missing dates. The updated query would now look something like:

SELECT
    COUNT(o.order_id) AS xAxis,
    WEEK(t.dt) AS yAxis,
    MIN(t.dt) AS week_start,
    MAX(t.dt) AS week_end
FROM
(
    SELECT '2020-01-01' AS dt UNION ALL
    SELECT '2020-01-02' UNION ALL
    ...
    SELECT '2020-12-31'
) t
LEFT JOIN orders o
    ON t.dt = o.created_at
WHERE
    o.created_at BETWEEN startDate AND endDate
GROUP BY
    WEEK(t.dt);

See here for more information on generating date tables in MySQL.