2

I want to display the data on a monthly and weekly basis, I already searched lot of stuff about this but what i found doesn't answer my question. Here is what my table looks like:

---------------
+ tblcomplain +
---------------
+ id          +
+ status      +
+ complain    +
+ dateposted  +
---------------
Christophvh
  • 12,586
  • 7
  • 48
  • 70
Trish Siquian
  • 495
  • 3
  • 11
  • 22

2 Answers2

2

if your column is dateposted is of type timestamp/datetime:

For Weekly Report:

select count(*) as totalWeeklycomplaints from tblcomplain group by week(dateposted);

For Monthly Report:

select count(*) as totalMothlycomplaints from tblcomplain group by month(dateposted);
Rehan Azher
  • 1,340
  • 1
  • 9
  • 17
  • Hi, does this method takes in consideration for different years data? – Wajdan Zahid Jul 26 '21 at 20:09
  • Hi, you can modify the query as following : select count(*) as totalMothlycomplaints, month(dateposted) as postedmonth, year(dateposted) as postyear from tblcomplain group by postedmonth, postyear; This should give you the count of complaints in each month of every year available in database. – Rehan Azher Oct 06 '21 at 09:52
0

For weekly basis

SELECT 
    *, EXTRACT(WEEK FROM add_date) AS w1
FROM
    product_alert_stock
ORDER BY w1 , add_date ASC

For Monthly basis

SELECT 
    *, EXTRACT(Month FROM add_date) AS m1
FROM
    product_alert_stock
ORDER BY m1 , add_date ASC
Komal Bandi
  • 67
  • 1
  • 7