How do you display data per week, not just for this month but the whole data of a user

Question

I have a sample data here:

id | quantity | date
1        1      2020-11-08
2        5      2020-11-07
3        3      2019-12-23
4        4      2019-12-22

How would I display this per week? Sample output:

November 1-8, 2020 (Latest):
quantity | date
1          2020-11-08
5          2020-11-07

December 16-22, 2019:
quantity | date
3          2019-12-22

December 23-29, 2019
quantity | date
3          2019-12-23

This problem got me stuck for 2 hours now. A short example to help me grasp on how to approach this would be much appreciated. Thanks in advance.

Note that it's unusual for a column called 'id' to be anything other than a PRIMARY KEY — Strawberry, Nov 08 '20 at 09:44
oh yeah, my bad that was supposed to be auto incremented let me edit that real quick. — sabu monkey, Nov 08 '20 at 12:25

score 0 · Accepted Answer · answered Nov 08 '20 at 08:54

You can add another column to your SQL result which defines a week the data is defined in:

SELECT quantity, date, CONCAT(YEAR(timestamp), '/', WEEK(timestamp))
FROM {yourTable}

By using this you get a column with week and you can do further transformations with this as you need.

Also year is first in the week 'name' so you can order by it.

How do you display data per week, not just for this month but the whole data of a user

1 Answers1