CODEWITHSUNDEEP
mysqlphpmyadmin
Arif
Arif

Reputation: 1211

Fetch mysql data from start till current month

I have a mysql table tbl_track with a lot of records

User_id  |  User_country  |  Datecreated (timestamp)
1        |3               |  2012-09-09 09:37:03
2        |7               |  2012-10-09 16:22:36

Now i want a result in a way like this (from starting month to current month)

September | September & October
1 record  | 2 records

I try this, but the result is opposite, from current month to start of month i.e (october, october+september, october+september+august). Also i wrote seperate query for each

My Queries for Current Month is

SELECT * FROM tbl_track 
  WHERE YEAR(`date_created`) = YEAR(CURRENT_DATE) 
  AND MONTH(`date_created`) = MONTH(CURRENT_DATE)

My Query for Current & Previous month is

SELECT * as count FROM tbl_track 
 WHERE YEAR(`date_created`) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
 AND MONTH(`date_created`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

Upvotes: 0

Views: 1066

Answers (2)

user254875486
user254875486

Reputation: 11240

As far as I know there is no easy way to do this in a query. However, you can use something like this: 

SELECT COUNT(1), MONTH(date_created) AS mm, YEAR(date_created) AS yy
FROM tbl_track
GROUP BY mm, yy

That gives you a list of the number of records per month. Based on your phpmyadmin tag I'm assuming your gonna use the result set in a PHP script, so maybe you can do the cummulation of the amounts from previous months in that script?

Upvotes: 1

Bhavik Shah
Bhavik Shah

Reputation: 5183

Your query for current and previous month just extracts the records for previous month

SELECT * as count FROM tbl_track 
WHERE YEAR(`date_created`) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
 AND MONTH(`date_created`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

I think you should change it to

SELECT count(*) as countofrec FROM tbl_track 
 WHERE YEAR(`date_created`) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
 AND ( MONTH(`date_created`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
        OR
       MONTH(`date_created`) = MONTH(CURRENT_DATE) )

Upvotes: 0

Related Questions