Nested query - looking for better solution

Question

Consider a sales application where we have two tables SALES and INTERNAL_SALES.

Table "SALES" references the number of transactions made by each sales person outside the company.
Table "INTERNAL_SALES" references the number of transactions made by each sales person inside the company to another sales person.

SALES Table: Each date has one entry against each sales person even if transactions are zero.

id |  day          | sales_person | number_of_transactions
1  | 2011-08-01    | Tom          | 1000
2  | 2011-08-01    | Ben          | 500
3  | 2011-08-01    | Anne         | 1500
4  | 2011-08-02    | Tom          | 0
5  | 2011-08-02    | Ben          | 800
6  | 2011-08-02    | Anne         | 900
7  | 2011-08-03    | Tom          | 3000
8  | 2011-08-03    | Ben          | 0
9  | 2011-08-03    | Anne         | 40

INTERNAL_SALES Table: This table logs only the transactions that were actually made between sales persons.

id | day        | sales_person_from | sales_person_to | number_of_transactions
 0 | 2011-08-01 | Tom               | Ben             | 10
 1 | 2011-08-01 | Tom               | Anne            | 20
 2 | 2011-08-01 | Ben               | Tom             | 50
 3 | 2011-08-03 | Anne              | Tom             | 30
 4 | 2011-08-03 | Anne              | Tom             | 30

Now the problem is to come up with total transactions by each sales person on a daily basis. The way I did this is:

SELECT day, sales_person, sum(num_transactions) from
(
  SELECT day, sales_person, number_of_transactions As num_transactions FROM sales;
  UNION
  SELECT day, sales_person_from As sales_person, sum(number_of_transactions) As num_transactions FROM internal_sales GROUP BY day, sales_person_from;
)
GROUP BY day, sales_person;

This is too slow and looks ugly. I am seeking a better solution. By the way the database being used in Oracle and I have no control over database except that I can run queries against it.

This is slow? It doesn't look at all ugly to me. I don't see a more straightforward solution. There's nothing to optimize here without the ability to modify the DB. — Dan Grossman
– Dan Grossman, Commented Aug 11, 2011 at 6:25

APC · Accepted Answer · 2011-08-11 09:08:36Z

7

There is no need to aggregate twice, and the union operator typically does an implicit unique sort which, again, in not necessary in your case.

SELECT day, sales_person, sum(num_transactions) from
(
    SELECT day, sales_person, number_of_transactions As num_transactions FROM sales;
    UNION ALL 
    SELECT day, sales_person_from, number_of_transactions FROM internal_sales;
)
GROUP BY day, sales_person;

Removing the intermediate aggregation and the unique sort should help a bit.

edited Aug 11, 2011 at 9:08

APC

147k20 gold badges180 silver badges289 bronze badges

answered Aug 11, 2011 at 6:30

Code Magician

24.1k7 gold badges63 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

APC Over a year ago

+1 There's no need to alias SALES_PERSON_FROM as the database takes the column name from the first SELECT statement and ignores thes subsequent ones.

APC Over a year ago

Also, the UNION can potentially return the wrong result. For instance, if sales rep TOM has made 10 external sales and 10 internal sales on the same day, their aggregated figure is 20; the UNION sort would treat those two rows as duplicates and so the aggregated figure would only be 10.

imtiaz Over a year ago

Thank you for your help. The explanation that goes with your solution increased my understanding of sql.

Collectives™ on Stack Overflow

Nested query - looking for better solution

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related