Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: