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

How calculate which day has the most sales for current year – Mysql

We have an existing system that has a lot of orders. I want to make a report that will show which days have the most sales from the beginning of the year 01.01.2023 to the current date. We researched examples via stackovefow but it doesn’t give us accurate information.

The report shows that Sunday has the most sales, which is not true. We have the lowest number of sales on weekends, while during working days Monday, Tuesday, Wednesday are the strongest days when we sell the most and then the most are entered into the system.

Can you check and tell me where I made a mistake?

SELECT   DAYNAME(o.created_at) day, AVG(o.id) sales
FROM     order o
WHERE    DAYOFWEEK(o.created_at) AND year(o.created_at) = '2023'
GROUP BY DAYNAME(o.created_at)
ORDER BY DAY(o.created_at)

enter image description here

Tables:

order

id           INT PK  AI
number       Varchar   NotNull
order_type   INT
created_at   timestamp

order_items

id               INT PK  AI
product_id       ID
order_id         INT
qty              INT
price            DECIMAL
created_at       timestamp

product

id               INT PK  AI
group_id         ID
supllier_id      INT
name             INT
created_at       timestamp
etc..

enter image description here

Tags:

2

Answers


  1. 0

    I think the following query provides the result that you want:

    SELECT DAYNAME(o.created_at) AS day_of_week, AVG(total_orders) AS avg_orders
FROM (
  SELECT DATE(o.created_at) AS order_date, COUNT(*) AS total_orders
  FROM order o
  WHERE YEAR(o.created_at) = YEAR('2023')
  GROUP BY DATE(o.created_at)
) subquery
GROUP BY DAYOFWEEK(o.created_at)
ORDER BY avg_orders DESC
LIMIT 1;

    This query takes all orders from the order table with dates in the specified year and then determines all order numbers for each day. It then calculates the average number of orders for each day and finally selects the day with the highest average number of orders.

    The DAYNAME() function is used to determine the name of each day, and the AVG() function is used to calculate the average number of orders for each day. The results are grouped according to the names of the days, and then arranged in descending order based on the average number of orders using the ORDER BY keyword. Finally, the day with the highest average number of orders is selected with the keyword LIMIT.

    Login or Signup to reply.
  2. 0

    You first have to count sales per day, then use AVG() on total sales to get the average by dayname

    with cte as (
  SELECT   created_at, count(1) sales
  FROM     order
  WHERE    year(created_at) = '2023'
  GROUP BY created_at
)
select DAYNAME(created_at) day, AVG(sales) as average
from cte
group by DAYNAME(created_at)

    Demo here

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

Back To Top
Search