CODEWITHSUNDEEP
mysqlsql
Brian Armstrong
Brian Armstrong

Reputation: 19863

MySQL best way to combine multiple queries

MySQL newbie here. I have these three separate queries. Each one groups by yearweek and selects one additional field.

-- tickets_by_hosts
select 
    yearweek(r.created_at) as week,
    count(zt.id) as tickets_by_hosts
from reservations r
inner join zendesk_tickets zt on zt.reservation_code = r.confirmation_code and zt.requester_id = r.host_id
where 
    r.created_at > '2011-8-20 00:00:00' and status != 0
group by yearweek(r.created_at)
order by week desc


-- tickets_by_guests
select 
    yearweek(r.created_at) as week,
    count(zt.id) as tickets_by_guests
from reservations r
inner join zendesk_tickets zt on zt.reservation_code = r.confirmation_code and zt.requester_id = r.guest_id
where 
    r.created_at > '2011-8-20 00:00:00' and status != 0
group by yearweek(r.created_at)
order by week desc

-- reservations
select 
    yearweek(r.created_at) as week,
    count(r.id) as reservations
from reservations r
where 
    r.created_at > '2011-8-20 00:00:00' and status != 0
group by yearweek(r.created_at)
order by week desc

What is the best way to combine these three queries so the results are all lined up by yearweek like:

week  tickets_by_hosts  tickets_by_guests  reservations
...   ...               ...                ...

Thank you! Have been googling around for this one but no luck so far.

Upvotes: 1

Views: 176

Answers (1)

Code Magician
Code Magician

Reputation: 23972

In your case, these do not need to be separate queries. You can implement conditional logic in the aggregate (SUM, COUNT etc) function using CASE.

select 
    yearweek(r.created_at) as week,
    SUM(CASE WHEN zt.requester_id = r.host_id THEN 1 ELSE 0 END ) as tickets_by_hosts,
    SUM(CASE WHEN zt.requester_id = r.guest_id THEN 1 ELSE 0 END ) as tickets_by_guests,
    COUNT(*) AS reservations,
from reservations r
inner join zendesk_tickets zt 
    on zt.reservation_code = r.confirmation_code 
where 
    r.created_at > '2011-8-20 00:00:00' and status != 0
group by yearweek(r.created_at)
order by week desc

The less efficient approach would be:

SELECT Q1.Week, Q1.Tickets_by_hosts, Q2.Tickets_by_guests, Q3.reservations
FROM (
        select 
            yearweek(r.created_at) as week,
            count(zt.id) as tickets_by_hosts
        from reservations r
        inner join zendesk_tickets zt on zt.reservation_code = r.confirmation_code and zt.requester_id = r.host_id
        where 
            r.created_at > '2011-8-20 00:00:00' and status != 0
        group by yearweek(r.created_at)
        order by week desc
     ) Q1
     INNER JOIN (
                    select 
                        yearweek(r.created_at) as week,
                        count(zt.id) as tickets_by_guests
                    from reservations r
                    inner join zendesk_tickets zt on zt.reservation_code = r.confirmation_code and zt.requester_id = r.guest_id
                    where 
                        r.created_at > '2011-8-20 00:00:00' and status != 0
                    group by yearweek(r.created_at)
                    order by week desc
                ) Q2 
            ON Q1.week = Q2.Week
    INNER JOIN (
                select 
                    yearweek(r.created_at) as week,
                    count(r.id) as reservations
                from reservations r
                where 
                    r.created_at > '2011-8-20 00:00:00' and status != 0
                group by yearweek(r.created_at)
                order by week desc  
               ) Q3
            ON Q1.week = Q3.week

For the second example, I rewrote the query to use each of the examples you posted as a subquery (or derived table), then joined them together. In this case, though, the DB would be doing all the work of scanning the tables and calculating the aggregates multiple times, you would also have the work of taking the dynamic result sets and joining them together (because of the nature of these resultsets, you really wouldn't have much benefit from indexes. This second option is the wrong approach, but I include it so you know how to use derived table which may be helpful in the future.

Upvotes: 3

Related Questions