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!
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.
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