CODEWITHSUNDEEP
mysqlselect
Giorgio
Giorgio

Reputation: 1970

Combine results of two different queries into a single query

I have a MySQL table containing login logs. Each entry contains user email, IP address, timestamp and the login result (0 fail, 1 success).

+------------+------------------+------------+--------+
| ip         | email            | datetime   | result |
+------------+------------------+------------+--------+
| 2130706433 | [email protected] | 1426498362 | 0      |
| 2130706433 | [email protected] | 1426498363 | 1      |
| 2130706433 | [email protected] | 1426498364 | 0      |
| 1134706444 | [email protected] | 1426498365 | 0      |
+------------+------------------+------------+--------+

My goal is to create a unique query to extract the count of failed logins from a given timestamp, and the timestamp of the last login for [email protected]. In this case I would like to obtain (suppose for simplicity that all entries are after the required timestamp)

+--------+------------+
| count  | datetime   |
+--------+------------+
| 3      | 1426498364 |
+--------+------------+

Until now, I've created two separated queries to extract results separately

SELECT COUNT(result) as count FROM (SELECT result FROM accesslogs WHERE datetime>1426498360 AND result=0) as subt
SELECT MAX(datetime) as datetime FROM accesslogs WHERE email=`[email protected]`

Now I'm tring to combine them to get results with a single query. I was wondering about using JOIN statement, but I don't know a column where queries may join. What can I do?

Upvotes: 0

Views: 78

Answers (4)

user169600
user169600

Reputation:

Take a look at MySQLs GROUP BY and HAVING. See if this works for you:

Any email:

SELECT email, COUNT(resultSet) as loginAttempts, 
COUNT(resultSet) - SUM(resultSet) as failedAttempts
FROM accesslogs 
GROUP BY email;

Specific email:

SELECT COUNT(result) as count, MAX(logDate) FROM accesslogs 
GROUP BY email
HAVING email='[email protected]';

Upvotes: 0

SlavM
SlavM

Reputation: 26

You could use UNION ALL but both queries must have same columns. I would suggest add text column to each query descibing type of value in other column like:

SELECT COUNT(result) as count, 'count' AS TYPE FROM (SELECT result FROM accesslogs WHERE datetime>1426498360 AND result=0) as subt

UNION ALL

SELECT MAX(datetime) as datetime, 'max' AS TYPE FROM accesslogs WHERE email='[email protected]'

EDIT: Sorry but I realised that this is more what you are trying to do (this is t-sql code but should work)

    SELECT a.email, a.count, b.datetime FROM (
    SELECT COUNT(result) as count, email FROM (SELECT result, email FROM 
    accesslogs AND result=0) as subt
    GROUP BY email) a
    JOIN (
    SELECT MAX(datetime) as datetime , email FROM accesslogs  GROUP BY email) b  
    on a.email = b.email
    WHERE a.email='[email protected]'
    AND a.datetime >'2012-12-17'

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can use conditional aggregation for this, something as

select 
sum( 
  case 
   when result=0 and datetime>1426498360 
   then  1 else 0 end 
) as `count`,
max(
   case 
   when email = '[email protected]' then datetime end
) as datetime 
from accesslogs ;

Here is an example 

mysql> select * from test ;
+------------+------------------+------------+--------+
| ip         | email            | datetime   | result |
+------------+------------------+------------+--------+
| 2130706433 | [email protected] | 1426498362 |      0 |
| 2130706433 | [email protected] | 1426498363 |      1 |
| 2130706433 | [email protected] | 1426498364 |      0 |
| 1134706444 | [email protected] | 1426498365 |      0 |
+------------+------------------+------------+--------+
4 rows in set (0.00 sec)

    mysql> select 
    -> sum( 
    ->   case 
    ->    when result=0 and datetime>1426498360 
    ->    then  1 else 0 end 
    -> ) as `count`,
    -> max(
    ->    case 
    ->    when email = '[email protected]' then datetime end
    -> ) as datetime 
    -> from test ;
+-------+------------+
| count | datetime   |
+-------+------------+
|     3 | 1426498364 |
+-------+------------+
1 row in set (0.00 sec)

Upvotes: 1

amow
amow

Reputation: 2223

Try

select * from (selete statement a) a join (selete statement b) b

Upvotes: 0

Related Questions