Row calculations using a derived table

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

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top