Ask the Expert

Row calculations using a derived table

My Cross Tab query is:

select count(*)                  as Total
     , sum( case when Prov = 'AB'
             then Quantity else 0 end ) as AB
     , sum( case when Prov = 'BC'
             then 1 else 0 end ) as BC
  from mytable

Is it possible to achieve an output like this?

Total  AB  BC  Others    
 150   75  25   25

    Requires Free Membership to View

Yes.

In your example, you sum Quantity for the AB count, but 1s and 0s for the BC count. Also, your example doesn't add up (150-75-25 does not equal 25). I shall assume these were typos, and just answer the issue of calculating the Others column.

One way to do it is to use another CASE expression:

select count(*)                  as Total
     , sum( case when Prov = 'AB'
             then 1 else 0 end ) as AB
     , sum( case when Prov = 'BC'
             then 1 else 0 end ) as BC
     , sum( case when Prov in ('AB','BC')
             then 0 else 1 end ) as Others
  from mytable

To avoid the unnecessary extra CASE evaluation and summing, you can "reuse" the expressions already being calculated:

select count(*)                  as Total
     , sum( case when Prov = 'AB'
             then 1 else 0 end ) as AB
     , sum( case when Prov = 'BC'
             then 1 else 0 end ) as BC
     , count(*) 
     - sum( case when Prov = 'AB'
             then 1 else 0 end )
     - sum( case when Prov = 'BC'
             then 1 else 0 end ) as Others
  from mytable

If the optimizer is any good, it will recognize those individual terms and not calculate them twice.

Some databases might allow you use a column alias elsewhere in the query after it's been defined, but I personally would not rely on this. So the above query would simplify to:

select count(*)                  as Total
     , sum( case when Prov = 'AB'
             then 1 else 0 end ) as AB
     , sum( case when Prov = 'BC'
             then 1 else 0 end ) as BC
     , Total-AB-BC   as Others
  from mytable

The slickest approach, which has the advantage that it avoids all unnecessary CASE evaluations and summing, is to use a derived table:

select Total, AB, BC, Total-AB-BC as Others 
  from ( select count(*)               as Total
           , sum( case when Prov = 'AB'
                   then 1 else 0 end ) as AB
           , sum( case when Prov = 'BC'
                   then 1 else 0 end ) as BC
          from mytable )

Neat, eh?

For More Information


This was first published in May 2003

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: