devxlogo

Create Summary Tables Efficiently

Create Summary Tables Efficiently

Suppose you have a table that contains transactions for different products and clients. Each day you need to summarize this information. If you need to know the sales figures for each product, irrespective of the clients, the sales figures for each client regardless of product, and the normal grouping of product by client, it seems you need at least three separate queries and consequently three passes through your data. However, if you use the “with cube” keyword, you can obtain all the information with one pass through your data.

 select client ,product,sum(amount) as 'Sum'from transgroup by client, productwith cube

This will provide you with all the answers in one query.

devx-admin

Share the Post: