CODEWITHSUNDEEP
phpmysql
Sanju
Sanju

Reputation: 55

PHP/MySql compare data between last 7 days and before last 7 days

I am trying to compare the count of my enquiries for last 7 days data (24-10-2018 to 31-10-2018) with before that weeks data (16-10-2018 to 23-10-2018). My enquiry Mysql table structure is as follows:

=================================
id | enquiry_date | enquiry_desc
=================================
 1     16-10-2018    Test Data
 2     17-10-2018    Test Data
 3     18-10-2018    Test Data
 4     18-10-2018    Test Data
 5     20-10-2018    Test Data
 6     24-10-2018    Test Data
 7     25-10-2018    Test Data
 8     28-10-2018    Test Data
=================================

So the result should be 

Last week count (16-10-2018 to 23-10-2018) : 5
This Week count (24-10-2018 to 31-10-2018) : 3

Just to start can anyone give me a guideline on how to start with it. Can i achieve this by one query?

Upvotes: 1

Views: 130

Answers (1)

Deepak Tyagi
Deepak Tyagi

Reputation: 104

Hi Sanju this is not tested but its help you, First check your date format than try below 

SELECT count(id) AS last_week FROM table_name
WHERE enquiry_date >= date_sub(current_date, INTERVAL 14 day) 
AND enquiry_date < date_sub(current_date, INTERVAL 7 day)
UNION
SELECT count(id) AS this_week FROM table_name
WHERE enquiry_date >= date_sub(current_date, INTERVAL 7 day)

Upvotes: 1

Related Questions