CODEWITHSUNDEEP
mysqlsql
dev
dev

Reputation: 745

Combine 2 MySQL queries

I have this query 

SELECT COUNT(*) from `login_log` where from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 WEEK);

and the same one with 1 diff. it's not 1 WEEK , but 1 MONTH how can I combine those two and assign them to aliases?

Upvotes: 0

Views: 80

Answers (3)

Linial
Linial

Reputation: 1154

Even though it's pretty tough to understand what you ask:

If you want them in the same column use OR

SELECT COUNT(*) from 'login_log' where from_unixtime('date') >= DATE_SUB(NOW(), INTERVAL 1 WEEK) OR from_unixtime('date') >= DATE_SUB(NOW(), INTERVAL 1 MONTH) ;

If you don't want duplicate answers: use GROUP BY

Upvotes: 0

Prasanth
Prasanth

Reputation: 5258

Use the where condition with one month internal and add the same where condition with one week internal as a Boolean column return.

I mean

Select count (*) all_in_month, (from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 WEEK)) as in_week from `login_log` where from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 a MONTH) GROUP BY in_week;

P.s. haven't tested but afaik it should work

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I would do this with conditional aggregation:

SELECT SUM(from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 WEEK)),
       SUM(from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 MONTH))
FROM `login_log`;

MySQL treats boolean values as integers, with 1 being "true" and 0 being "false". So, using sum() you can count the number of matching values. (In other databases, you would do something similar using case.)

Upvotes: 3

Related Questions