Q

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

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

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