Union two queries PostgreSQL

Question

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.

"whyn I try to join these two" ... how? Please show the combined query. Also, what "query builder" are you using? — Craig Ringer
– Craig Ringer, Commented Jul 22, 2013 at 3:28

pstanton · Accepted Answer · 2013-07-22 03:57:02Z

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...

hanzpk · Accepted Answer · 2013-07-22 06:32:33Z

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

Collectives™ on Stack Overflow

Union two queries PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related