CODEWITHSUNDEEP
mysql
Lee Armstrong
Lee Armstrong

Reputation: 11450

MYSQL Query split by month

At the moment I have a MYSQL query that looks like this...

 SELECT distinct devid,username FROM auth_log WHERE MONTH(accesstime) = 1;

This would return all of January which is what I want. Is it possible to return an array or something like that as a single query for a whole year or date range but split by month thus saving me from having to run multiple queries like.

SELECT distinct devid,username FROM auth_log WHERE MONTH(accesstime) = 1;
SELECT distinct devid,username FROM auth_log WHERE MONTH(accesstime) = 2;
SELECT distinct devid,username FROM auth_log WHERE MONTH(accesstime) = 3;

Upvotes: 0

Views: 2188

Answers (3)

CarpeNoctumDC
CarpeNoctumDC

Reputation: 1760

SELECT distinct devid, username FROM auth_log WHERE MONTH(accesstime) >= 1 AND MONTH(accesstime) <= 12 ORDER BY MONTH(accesstime);

Could be cleaned up.. but just one of MANY variations that could be used

Upvotes: 0

DRapp
DRapp

Reputation: 48179

SELECT distinct MONTH(accesstime), devid,username 
   FROM auth_log 
   WHERE MONTH(accesstime) in ( 1, 2, 3 )

or

where month(accesstime) between 1 and

12

Upvotes: 0

Raj More
Raj More

Reputation: 48058

How about just get

SELECT DISTINCT
    MONTH(accesstime) AccessTimeMonth, 
    devid,
    username 
FROM auth_log

and do your filtering work in the front end?

Upvotes: 1

Related Questions