Reputation: 919
I have two queries. One:
select driver_id,count(*) as minors from zendesk_tickets
where city = 'Los Angeles'
and (tags like '%la_driver_quality_inefficientroute%'
or tags like '%la_driver_quality_attitude%'
or tags like '%la_driver_quality_dirtycar%'
or tags like '%la_driver_quality_hygiene%'
or tags like '%la_driver_quality_wronguser%'
or tags like '%la_driver_quality_starttripearly%'
or tags like '%la_driver_quality_arrivingearly%'
or tags like '%la_driver_quality_didnotanswer%'
or tags like '%la_driver_quality_nodoor%'
or tags like '%la_driver_quality_cigarettes%'
or tags like '%la_driver_quality_startstop%'
or tags like '%la_driver_quality_arguing%'
or tags like '%la_driver_quality_refusedestination%'
or tags like '%la_driver_quality_onphoneontrip%'
)
and driver_id is not null
group by driver_id
order by minors desc
Which outputs:
driver_id minors
1234 2
1243 3
etc etc
And another query:
select driver_id,count(*) as majors from zendesk_tickets
where city = 'Los Angeles'
and (tags like '%la_driver_quality_acceptcash%'
or tags like '%la_driver_quality_solicitingclient%'
or tags like '%la_driver_quality_majordisrespect%'
or tags like '%la_driver_quality_physicalaltercation%'
or tags like '%la_driver_quality_sexualharassment%'
or tags like '%la_driver_quality_majordrivingviolation%'
or tags like '%la_driver_quality_dui%'
or tags like '%la_driver_quality_usingaccount%'
or tags like '%la_driver_quality_expiredinfo%'
)
and driver_id is not null
group by driver_id
order by majors desc
Which outputs:
driver_id majors
1234 1
1243 2
etc etc
When I try to join these two (left join) it still only returns the driver ids that are in the first query (minors). I want to essentially union these queries, but the union function isnt working for me in my querybuilder. Any suggestions on how to improve the structure of these two queries and return all of the data of minors and majors into one table which will output:
driver_id minors majors
1234 1 3
1243 2 4
etc etc etc.
Upvotes: 0
Views: 127
Reputation: 123
select data.driver_id, sum(data.minor), sum(data.major)
from (
select driver_id,
case when
tags like '%la_driver_quality_inefficientroute%'
or tags like '%la_driver_quality_attitude%'
or tags like '%la_driver_quality_dirtycar%'
or tags like '%la_driver_quality_hygiene%'
or tags like '%la_driver_quality_wronguser%'
or tags like '%la_driver_quality_starttripearly%'
or tags like '%la_driver_quality_arrivingearly%'
or tags like '%la_driver_quality_didnotanswer%'
or tags like '%la_driver_quality_nodoor%'
or tags like '%la_driver_quality_cigarettes%'
or tags like '%la_driver_quality_startstop%'
or tags like '%la_driver_quality_arguing%'
or tags like '%la_driver_quality_refusedestination%'
or tags like '%la_driver_quality_onphoneontrip%' then 1 else 0 end minor,
case when
tags like '%la_driver_quality_acceptcash%'
or tags like '%la_driver_quality_solicitingclient%'
or tags like '%la_driver_quality_majordisrespect%'
or tags like '%la_driver_quality_physicalaltercation%'
or tags like '%la_driver_quality_sexualharassment%'
or tags like '%la_driver_quality_majordrivingviolation%'
or tags like '%la_driver_quality_dui%'
or tags like '%la_driver_quality_usingaccount%'
or tags like '%la_driver_quality_expiredinfo%'then 1 else 0 end major
from zendesk_tickets
where city = 'Los Angeles'
and driver_id is not null) data
group by data.driver_id
Upvotes: 0
Reputation: 36699
you need to use an outer join.
see this document for details.
Try something like this:
select min.driver_id, majors, minors from (
select driver_id,count(*) as minors from zendesk_tickets
where city = 'Los Angeles'
and (tags like '%la_driver_quality_inefficientroute%'
or tags like '%la_driver_quality_attitude%'
or tags like '%la_driver_quality_dirtycar%'
or tags like '%la_driver_quality_hygiene%'
or tags like '%la_driver_quality_wronguser%'
or tags like '%la_driver_quality_starttripearly%'
or tags like '%la_driver_quality_arrivingearly%'
or tags like '%la_driver_quality_didnotanswer%'
or tags like '%la_driver_quality_nodoor%'
or tags like '%la_driver_quality_cigarettes%'
or tags like '%la_driver_quality_startstop%'
or tags like '%la_driver_quality_arguing%'
or tags like '%la_driver_quality_refusedestination%'
or tags like '%la_driver_quality_onphoneontrip%'
)
and driver_id is not null
group by driver_id
order by minors desc
) as min left outer join (
select driver_id,count(*) as majors from zendesk_tickets
where city = 'Los Angeles'
and (tags like '%la_driver_quality_acceptcash%'
or tags like '%la_driver_quality_solicitingclient%'
or tags like '%la_driver_quality_majordisrespect%'
or tags like '%la_driver_quality_physicalaltercation%'
or tags like '%la_driver_quality_sexualharassment%'
or tags like '%la_driver_quality_majordrivingviolation%'
or tags like '%la_driver_quality_dui%'
or tags like '%la_driver_quality_usingaccount%'
or tags like '%la_driver_quality_expiredinfo%'
)
and driver_id is not null
group by driver_id
order by majors desc
) as maj on (min.driver_id=maj.driver_id);
of course, in practice you would create a view for your subqueries or some other way of tidying up this query...
Upvotes: 1