CODEWITHSUNDEEP
mysqlsql
Anita Mourya
Anita Mourya

Reputation: 115

merge two separate mysql queries

need to merge (maybe UNION ALL) this two mysql queries..

select users.name as 'Agent', min(agent_activities.created_at) as 'Login Time' 
from agent_activities, users
where 
agent_activities.agent_id = users.id 
and date(agent_activities.created_at) = curdate() 
and agent_activities.activity='login' 
group by agent_activities.agent_id 
order by agent_activities.agent_id asc;

select users.name as 'Agent', max(agent_activities.created_at) as 'Logout Time' 
from agent_activities, users
where 
agent_activities.agent_id = users.id 
and date(agent_activities.created_at) = curdate() 
and agent_activities.activity='logout' 
group by agent_activities.agent_id 
order by agent_activities.agent_id asc;

Now result is

Agent       | Login Time
test Admin  | 2018-09-27 10:26:54
Agent 1     | 2018-09-27 11:43:44
Hari        |  2018-09-27 11:10:41

Agent       | Logout Time
test Admin  | 2018-09-27 11:43:41
Agent 1     | 2018-09-27 11:45:04
ttt         | 2018-09-27 11:21:06
Hari VH     | 2018-09-27 15:18:04

Desired result

Agent       | Login Time           | Logout Time
test Admin  | 2018-09-27 10:26:54  | 2018-09-27 11:43:41
Agent 1     | 2018-09-27 11:43:44  | 2018-09-27 11:45:04
Hari        |  2018-09-27 11:10:41 | 2018-09-27 15:18:04

Please help,..................................

Thanks in advance.

Upvotes: 0

Views: 52

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do what you want with conditional aggregation:

select u.name as Agent,
       max(case when aa.activity = 'login' then aa.created_at end) as Login_Time,
       max(case when aa.activity = 'logout' then aa.created_at end) as Logout_Time 
from agent_activities aa join
     users u
     on aa.agent_id = u.id 
where aa.created_at >= curdate() and
      aa.created_at < curdate() + interval 1 day
group by u.name 
order by u.name  asc;

Notes:

  • Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.
  • Table aliases make the query easier to write and to read. Use abbreviations for the table names.
  • Using functions on columns generally impedes the use of indexes, so I replaced date(created_at) with simpler date comparisons.
  • Your GROUP BY columns should match the unaggregated columns in the SELECT.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Use conditional min/max, and consider the min created_at only when the activity is login, and vice-versa for logout. The reason this works is that the default ELSE value (not explicitly shown) is NULL, which is ignored by both MIN and MAX.

SELECT
    u.name as 'Agent',
    MIN(CASE WHEN a.activity = 'login'  THEN a.created_at END) AS 'Login Time' 
    MAX(CASE WHEN a.activity = 'logout' THEN a.created_at END) AS 'Logout Time' 
FROM agent_activities a
INNER JOIN users u
    ON a.agent_id = u.id 
WHERE
    DATE(a.created_at) = CURDATE()
GROUP BY
    a.agent_id, u.name
ORDER BY
    a.agent_id, u.name;

Some notes:

I converted your implicit join to an explicit inner join, with an ON clause. Also, I introduced table aliases into the query, which leaves it much easier to read.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37493

Try with conditional aggregation:

select users.name as 'Agent', min(case when agent_activities.activity='login' then agent_activities.created_at end) as 'Login Time' ,
max(case when agent_activities.activity='logout' then agent_activities.created_at end) as 'Logout Time'
from agent_activities inner join users
on agent_activities.agent_id = users.id 
and date(agent_activities.created_at) = curdate() 
group by users.name 
order by users.name asc

Upvotes: 1

Related Questions