CODEWITHSUNDEEP
mysql
Gaurav Parashar
Gaurav Parashar

Reputation: 1592

sql query with changing month

I need to query a database which will return the number of people subscribed to a particular service during that month. I use the below query 

select  subscriberid, count(*) from ABC where updated time like '2013-05-%' ;

In this query I need to update the Updatedtime field to be 2013-06-% when the next month comes and then 07 when the next to next month comes. I want the query to be updated automatically when the next month comes instead of manually changing it every time.

Also note that I want the data for a particular month at a time, so please don't suggest grouping by month as an answer.

Upvotes: 2

Views: 118

Answers (4)

juanreyesv
juanreyesv

Reputation: 853

Use the following query:

SELECT subscribersid, updated, COUNT(*) from subscribers 
   WHERE YEAR(updated) = YEAR(NOW()) 
      AND MONTH(updated) = MONTH(NOW())

You can see it working in this SQL Fiddle

Hope this helps

Upvotes: 0

peterm
peterm

Reputation: 92785

One way to do it

SELECT subscriberid, COUNT(*) 
  FROM ABC 
 WHERE  YEAR(updated_time) =  YEAR(CURDATE()) 
   AND MONTH(updated_time) = MONTH(CURDATE())

or

SELECT subscriberid, COUNT(*) 
  FROM ABC 
 WHERE updated_time BETWEEN ADDDATE(LAST_DAY(SUBDATE(CURDATE(), INTERVAL 1 MONTH)), 1) 
                        AND LAST_DAY(CURDATE())

Upvotes: 3

Fabio
Fabio

Reputation: 23490

I think you can use DATE_FORMAT function

 SELECT subscriberid, count(*) as total
 FROM ABC 
 WHERE DATE_FORMAT(updated_time, "%Y-%m") = "2013-05";

Upvotes: 0

h2ooooooo
h2ooooooo

Reputation: 39532

The following should work fine:

SELECT 
    subscriberid, 
    count(*) 
from 
    ABC 
where 
    updatedtime LIKE CONCAT(DATE_FORMAT(NOW(),'%Y-%m'), '-%')

Upvotes: 1

Related Questions