Q

Totals for one customer compared with totals for all

I am trying to retrieve records where I can compare two sums. For example, if customer 123 purchased a quantity of 10 Widget Model A's over a one month period, I want the same row to show how many Widget Model A's we sold to all customers. A widget is defined by five unique fields. And, even if we sold other Widget Models, I don't want to pull any other Widget Models if company 123 did not purchase them that month. All of the fields...

are retrieved from one table.

Since a widget is defined by five unique fields, we have to code the values for these, as well as for the customer and the time period. Allow me to invent the names of the five fields:

select sum(quantity) as cust_qty
     , ( select sum(quantity)
           from orders 
          where product_class  = 'General'
            and product_line   = 'Widgets'
            and product_type   = 'Model A'
            and product_status = 'active'
            and product_flag   = 0 
            and purchdate >= '2005-02-01'
            and purchdate <  '2005-03-01'
       ) as all_qty
  from orders 
 where custid = 123
   and product_class  = 'General'
   and product_line   = 'Widgets'
   and product_type   = 'Model A'
   and product_status = 'active'
   and product_flag   = 0 
   and purchdate >= '2005-02-01'
   and purchdate <  '2005-03-01'

Notice the subquery in the SELECT list. This is called a scalar subquery, because it produces a single (scalar) value, and thus can be used instead of a single column value on each row. The database optimizer will execute a scalar subquery like this, which is non-correlated, i.e. not tied to a particular row in the main query, only once, ahead of the main query, so that its result, the sum for all customers, can be inserted into each row selected by the main query.

The subquery could also have been written as a correlated subquery, to make it a little easier to write the query for any product:

select sum(quantity) as cust_qty
     , ( select sum(quantity)
           from orders 
          where product_class  = O.product_class
            and product_line   = O.product_line
            and product_type   = O.product_type
            and product_status = O.product_status
            and product_flag   = O.product_flag
            and purchdate >= '2005-02-01'
            and purchdate <  '2005-03-01'
       ) as all_qty
  from orders as O

 where custid = 123
   and product_class  = 'General'
   and product_line   = 'Widgets'
   and product_type   = 'Model A'
   and product_status = 'active'
   and product_flag   = 0 
   and purchdate >= '2005-02-01'
   and purchdate <  '2005-03-01'

But now the optimizer might execute the scalar subquery once for each row that satisfies the criteria of the WHERE clause. This might not be as efficient, unless the optimizer realizes that there is an implicit GROUP BY and that there is actually only one group. So you may actually want to code the five widget field values twice.

Note also that it would not be quite so easy to correlate the time period, so it's usually more efficient just to code the time period end-points twice too.


This was first published in March 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close