Alternative approach to multiple left joins

Question

I have the following queries to count sales by route

SELECT DISTINCT q.sales_route, 
                y.yesterday, 
                t.today 
FROM   tblquotesnew q 
       left join (SELECT tblquotesnew.sales_route, 
                         Count(tblquotesnew.sales_route) AS Yesterday 
                  FROM   tblquotesnew 
                  WHERE  tblquotesnew.date_sent_to_registrations = 
                         Trunc(SYSDATE - 1) 
                         AND sales_route IS NOT NULL 
                  GROUP  BY tblquotesnew.sales_route) y 
              ON q.sales_route = y.sales_route 
       left join (SELECT tblquotesnew.sales_route, 
                         Count(tblquotesnew.sales_route) AS Today 
                  FROM   tblquotesnew 
                  WHERE  tblquotesnew.date_sent_to_registrations = 
                         Trunc(SYSDATE) 
                         AND sales_route IS NOT NULL 
                  GROUP  BY tblquotesnew.sales_route) t 
              ON q.sales_route = t.sales_route

I then have 6 other left joins to count current and previous week, month, and year.

This approach does work, but I was wondering if this is a more efficient (in terms of lines of code) way of pulling together this data?

score 4 · Accepted Answer · answered Sep 25 '17 at 10:13

I think you just need conditional aggregation:

select q.sales_route, 
       sum(case when q.date_sent_to_registrations = trunc(SYSDATE - 1)
                then 1 else 0
             end) as yesterday,
       sum(case when q.date_sent_to_registrations = trunc(SYSDATE)
                then 1 else 0
             end) as today
from tblquotesnew q 
group by sales_route

Radim Bača · Answer 2 · 2017-09-25T10:21:13.633

You may use conditional aggregation

SELECT sales_route, 
            sum(CASE WHEN date_sent_to_registrations = Trunc(SYSDATE) 
                          AND sales_route IS NOT NULL 
                     THEN 1 ELSE 0 END) today, 
            sum(CASE WHEN date_sent_to_registrations = Trunc(SYSDATE - 1) 
                          AND sales_route IS NOT NULL 
                     THEN 1 ELSE 0 END) yesterday 
FROM   tblquotesnew 
GROUP BY sales_route

conditional aggregation leads to one sequential scan of your table which may be ok in many cases. An alternative solution is to use subqueries behind SELECT which may be sometimes more efficient. For example, if you access small subselect of data and you can create indexes to support it.

Alternative approach to multiple left joins

2 Answers2