MySQL PHP select rows count in date range

Question

I have a MySQL table for product orderings named TABLE1.

Date means the date purchase has been made

The table has other columns that currently have no influence.

PRODUCT_ID | DATE      | other columns
    3      |2018-02-01 | other values
    3      |2018-02-03 | other values
    3      |2018-02-07 | other values
    3      |2018-02-07 | other values
    3      |2018-03-02 | other values

I know that the first time the product 3 has been ordered, is 2018-02-01

SELECT DATE FROM TABLE1 WHERE PRODUCT_ID = '3' ORDER BY DATE ASC LIMIT 1

How do I select count of product orderings per day within range of 2018-02-01 and 2019-03-16 (today) so that I could get a table like that:

DATE       | ORDERS_PER_DAY
2018-02-01 | 1
2018-02-02 | 0
2018-02-03 | 1
...
2018-02-07 | 2
...
2018-03-02 | 1
...
2018-03-15 | 0
2018-03-16 | 0

Thanks for help!

Satish Saini · Accepted Answer · 2019-03-16 18:16:59Z

4

You can simply use GROUP BY clause to do it.

SELECT `DATE`, COUNT(`PRODUCT_ID`) AS ORDERS_PER_DAY
FROM TABLE1
WHERE `DATE` BETWEEN '2018-02-01' AND CURDATE() 
GROUP BY `DATE`

This query will result in filtering the records on your required date range and then grouping it by each day where there is data.

edited Mar 16, 2019 at 18:16

answered Mar 16, 2019 at 17:57

Satish Saini

2,9783 gold badges27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

userxöa Over a year ago

Thanks for this. How could I get the response that in day x there will be 0 product sold? DATE | ORDERS_PER_DAY 2018-02-04 | 0

Satish Saini Over a year ago

You can write a loop in code PHP code and check ORDERS_PER_DAY == 0 to find dates where orders count is 0.

Satish Saini Over a year ago

@userxöa I have modified the query. Please use that.

Oxymoron · Accepted Answer · 2019-03-16 17:55:23Z

1

My syntax may not be exactly correct, but could you try something like this using the GROUP BY clause.

SELECT DATE, COUNT(*) AS ORDERS_PER_DAY
FROM TABLE1
GROUP BY DATE, PRODUCT_ID
HAVING PRODUCT_ID = '3'

you can read more about this here: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

answered Mar 16, 2019 at 17:55

Oxymoron

1,4623 gold badges20 silver badges49 bronze badges

1 Comment

Barmar Over a year ago

Why not WHERE product_id = 3?

Collectives™ on Stack Overflow

MySQL PHP select rows count in date range

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related