Make the most of table scans with multiple summaries

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

    Requires Free Membership to View

This was first published in April 2001

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.