My challenge of the day: a query that returns aggregate values. And that can be manipulated dynamically to sometimes aggregate grouped by one dimension, sometimes along multiple or another single dimensiopn. The query will be used to build a report page on that displays a table with rows returned from the query. In this page, the user can hide/display columns. Whenever a column is hidden, the query should no longer group by the columnn. However, it should still be the same query.

In this article, we will look at a way to bring this dynamic, conditional aggregation behavior into a SQL query. How the query will perform aggregating on different levels without change to the query itself. We will use wildly interesting concepts such as the database context, the CASE statement and last but not least, the CUBE (aggregate) operator.

In a subsequent article we will also take a look at the web page that we create on top of this query. (more…)