I have a table and i need this sql qurry to diplay=y total number of postcode using this week and date wise
| id | postcode | date |
|---|---|---|
| 1 | e16 2ez | 2022-09-10 |
| 2 | e17 2ez | 2022-09-12 |
| 3 | e18 2ez | 2022-09-13 |
| 4 | e19 2ez | 2022-09-19 |
| 5 | e19 2ez | 2022-09-19 |
| 6 | c16 2ez | 2022-09-19 |
| 21 | c16 2ez | 2022-09-20 |
| 22 | c16 2ez | 2022-09-20 |
| 8 | d16 2ez | 2022-09-20 |
| 9 | d16 2ez | 2022-09-21 |
| 10 | dd16 2ez | 2022-09-21 |
| 11 | d16 2ez | 2022-09-22 |
| 12 | d16 2ez | 2022-09-23 |
| 13 | d16 2ez | 2022-09-24 |
| 14 | d16 2ez | 2022-09-24 |
| 15 | d16 2ez | 2022-09-24 |
| 16 | e16 2ez | 2022-09-26 |
| 17 | e16 2ez | 2022-09-26 |
| 18 | e16 2ez | 2022-09-28 |
Output i need this week date wise date
| total count | date |
|---|---|
| 3 | 2022-09-19 |
| 3 | 2022-09-20 |
| 2 | 2022-09-21 |
| 1 | 2022-09-22 |
| 1 | 2022-09-23 |
| 3 | 2022-09-24 |
CodePudding user response：
This is a fairly common SQL issue. Did you search before posting? Because the query below must work right...?
SELECT count(id) as 'total count' FROM `yourTable` group by date
CodePudding user response：
Try the following:
SELECT COUNT(*) 'total count' , date
FROM
table_name
WHERE date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 6 DAY)
GROUP BY date
ORDER BY date
See a demo.