I have a SQL table similar to below
| id | item | date |
|---|---|---|
| 1 | item1 | date1 |
| 2 | item2 | date1 |
| 3 | item3 | date3 |
| 4 | item4 | date4 |
| 5 | item3 | date4 |
If wanted to print how many items exist for each date, I can do a simple group by and sum on date and I will get something like below
| date | count |
|---|---|
| date1 | 2 |
| date3 | 1 |
| date4 | 2 |
My goal is to have data displayed in below form for each date(count) and also for date-7 days
| date | count | date-7 | count(date-7) |
|---|---|---|---|
| date1 | 2 | date1-7 | count for date1-7 |
| date3 | 1 | date3-7 | count for date3-7 |
| date4 | 2 | date4-7 | count for date4-7 |
How do I frame such a query?