Reputation: 5105
I have a working query below but I need to select first and last names for these agents in my select statement, without any real value to join the table on.
My other table is ambition.ambition_users and it has first_name, last_name and extension. I have the subqueries pulling the extensions correctly and treating it as id
. However, in my main select, I want to also select first_name and last_name. I can't seem to find a way to do this with a simple subquery and I don't have anything to join the table on UNLESS I'm able to join my created id
on ambition_users.extention.
What's the best way to achieve pulling first and last name without affecting the aggregations of the current query?
Here's the working query:
select
case
when callingpartyno in (select extension from ambition.ambition_users)
then callingpartyno
when finallycalledpartyno in (select extension from ambition.ambition_users)
then finallycalledpartyno
end as id
-- this is where i want to select first_name and last_name from ambition.ambition_users
, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(if(legtype1 = 1,1,0)) as total_outbound
, sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
, sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
, sum(if(legtype1 = 1, 1, 0)) + -- outbound calls
sum(if(legtype1 = 2, 1, 0)) as total_calls
, now() as time_of_report
, curdate() as date_of_report
from
ambition.session a
join ambition.callsummary b
on a.notablecallid = b.notablecallid
where
date(a.ts) >= curdate()
and (
callingpartyno in (select extension from ambition.ambition_users
)
or finallycalledpartyno in (select extension from ambition.ambition_users
)
)
group by
id;
Upvotes: 0
Views: 106
Reputation: 1450
Why you need so many subqueries. You can use a left join and take out all other subqueries.
select
COALESCE( callingpartyno, finallycalledpartyno) as id
-- if extension is not equal to callingpartyno, it will return null because its a left join. If you use coalesce you can get the first non null value.
, max(c.firstname)
, max(c.lastname)
-- if you use max you need not put that in group by clause
, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(if(legtype1 = 1,1,0)) as total_outbound
, sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
, sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
, sum(if(legtype1 = 1, 1, 0)) + -- outbound calls
sum(if(legtype1 = 2, 1, 0)) as total_calls
, now() as time_of_report
, curdate() as date_of_report
from
ambition.session a
join ambition.callsummary b
on a.notablecallid = b.notablecallid
---- adding a left join
left join
ambition.users c
on c.extension = callingpartyno or c.extension = finallycalledpartyno
where
date(a.ts) >= curdate()
group by id;
Upvotes: 1
Reputation: 3592
Instead of using same subquery again & again, i'll advise you to store the result of subquery in a variable.
Also you can write different queries for callingpartyno
and finallycalledpartyno
and later merged results with UNION
as follows:
select @ext := Group_concat(distinct extension separator ',') from ambition.ambition_users;
select tmp.*,
(select firstn from ambition.ambition_users where tmp.id = extension) as First_Name,
(select lastn from ambition.ambition_users where tmp.id = extension) as Last_Name
From
(select
callingpartyno as id
, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(if(legtype1 = 1,1,0)) as total_outbound
, sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
, sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
, sum(if(legtype1 = 1, 1, 0)) + -- outbound calls
sum(if(legtype1 = 2, 1, 0)) as total_calls
, now() as time_of_report
, curdate() as date_of_report
from
ambition.session a
join ambition.callsummary b
on a.notablecallid = b.notablecallid
where
date(a.ts) >= curdate()
and
callingpartyno in (@ext)
group by callingpartyno)
UNION
(select
finallycalledpartyno as id
, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(if(legtype1 = 1,1,0)) as total_outbound
, sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
, sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
, sum(if(legtype1 = 1, 1, 0)) + -- outbound calls
sum(if(legtype1 = 2, 1, 0)) as total_calls
, now() as time_of_report
, curdate() as date_of_report
from
ambition.session a
join ambition.callsummary b
on a.notablecallid = b.notablecallid
where
date(a.ts) >= curdate()
and
finallycalledpartyno in (@ext)
group by finallycalledpartyno)
) tmp;
Upvotes: 0
Reputation: 2159
I didn't get relationship among your tables, but you can use below logic to get first and last name with subqueries within the select statement
select
case
when callingpartyno in (select extension from ambition.ambition_users)
then callingpartyno
when finallycalledpartyno in (select extension from ambition.ambition_users)
then finallycalledpartyno
end as id
-- this is where i want to select first_name and last_name from ambition.ambition_users
Max((select firstname from ambition.ambition_users as t1 where t1. Extension=b.callingpartyno)),
Max((select lastname from ambition.ambition_users as t1 where t1. Extension=b.callingpartyno)),
, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(if(legtype1 = 1,1,0)) as total_outbound
, sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
, sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
, sum(if(legtype1 = 1, 1, 0)) + -- outbound calls
sum(if(legtype1 = 2, 1, 0)) as total_calls
, now() as time_of_report
, curdate() as date_of_report
from
ambition.session a
join ambition.callsummary b
on a.notablecallid = b.notablecallid
where
date(a.ts) >= curdate()
and (
callingpartyno in (select extension from ambition.ambition_users
)
or finallycalledpartyno in (select extension from ambition.ambition_users
)
)
group by
id;
Upvotes: 1