This is a technique to obtain multiple summaries on different columns with a single pass through a table rather than multiple passes. It is very useful for efficient reporting.
In the code below, Total1 will be the sum of col1 where colA = value1. The trick is to multiply the row's column value with 1 when the row is of interest and zero when the row is not of interest. This will then only accumulate the values you want.
The real savings occur when you can include multiple summations in a single pass through the table rather than multiple passes.
This is especially useful in data warehouses where there are a LOT of rows to process. You want to limit the number of times you have to do a table scan.
SELECT Total1=SUM(col1 * (1 - abs(sign(colA-value1))) ,Total2=SUM(col1 * (1 - abs(sign(colB-value2))) ,Total3=SUM(col1 * (1 - abs(sign(colC-value3))) ,Total4=SUM(col2 * (1 - abs(sign(colD-value4))) ,Total5=SUM(col3 * (1 - abs(sign(colE-value5))) FROM tablename WHERE whereclause
For More Information
- What do you think about this tip? E-mail us at
- editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL gurus are waiting to answer your toughest questions.
This was first published in April 2001