Product selling records between dates

Question

I tried the following SQL statement in MySQL:

select tbl_product.*, count(*) total
from tbl_product 
where date(tbl_product.date) > date("2022-11-21") && date(tbl_product.date) > date("2022-11-29")
group by tbl_product.product_id

I want to output as the following image

Answer 1

Select columns not tables https://dev.mysql.com/doc/refman/8.0/en/select.html , mysql strings are enclosed in single quotes When to use single quotes, double quotes, and backticks in MySQL , convert strings to dates https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date , test date range - both your tests are >

select tbl_product.product_id, count() total 
from tbl_product 
where date(tbl_product.date) >= str_to_date('2022-11-21','%Y-%m-%d')) and 
      date(tbl_product.date) <= str_to_date('2022-11-29','%Y-%m-%d')) 
group by tbl_product.product_id