Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in April 2001

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.