Make the most of table scans with multiple summaries

Increase reporting efficiency by obtaining multiple summaries on different columns with a single pass through a table.

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


This was first published in April 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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