CODEWITHSUNDEEP
phpmysql
Serdar Nikola
Serdar Nikola

Reputation: 49

Reduce number of mysql count query for different time interval

I am using below query: 

$select_jan1 = "SELECT count(*) FROM users WHERE timeStamp BETWEEN '2015-01-01' and '2015-01-31'";
$select_feb1 = "SELECT count(*) FROM users WHERE timeStamp BETWEEN '2015-02-01' and '2015-02-28'";
$select_mar1 = "SELECT count(*) FROM users WHERE timeStamp BETWEEN '2015-03-01' and '2015-03-31'";

Is there way to put this 3 query in one ?

Thank You

Upvotes: 1

Views: 79

Answers (2)

phsaires
phsaires

Reputation: 2378

With whole months, I do so:

SELECT
count(*)
FROM
(SELECT
year(timestamp) AS year_, month(timestamp) AS month_
FROM
users) s
WHERE
s.year_ = 2015 AND s.month_ >=4

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You can put the values in three columns, using conditional aggregation:

SELECT SUM(timeStamp BETWEEN '2015-01-01' and '2015-01-31') as cnt_201501,
       SUM(timeStamp BETWEEN '2015-02-01' and '2015-02-28') as cnt_201502,
       SUM(timeStamp BETWEEN '2015-03-01' and '2015-03-31') as cnt_201503       
FROM users;

Do note that this logic ignores that values on the last day of each month. Better logic is:

SELECT SUM(timeStamp >= '2015-01-01' and timestamp < '2015-02-01') as cnt_201501,
       SUM(timeStamp >= '2015-02-01' and timestamp < '2015-03-01') as cnt_201502,
       SUM(timeStamp >= '2015-03-01' and timestamp < '2015-04-01') as cnt_201503       
FROM users
WHERE timeStamp >= '2015-01-01' and timeStamp < '2015-04-01';

Upvotes: 3

Related Questions