|
|
Posted by Erland Sommarskog on 09/02/07 22:05
(csimam@gmail.com) writes:
> Which one is better?
> 1. To perform aggregate queries on data, and use that data to home in
> and perform more detailed analysis? This gives result sets that are
> potentially filled with columns that contain the aggregate data
> repeated across many rows.
And you clarified:
> By the way, I meant here by homing in: within the same query - using
> nested SELECTS. For example, the inner query performs the aggregate
> analysis, and returns whatever data we are interested in, and then the
> outer query performs another SELECT on the same data, resulting in the
> aggregate data being distributed across 'detailed results' from the
> outer query.
Your question is quite abstract, but to take a concrete example, it
seems like you have sales data, and you want to aggregate this on
per month, product, state, etc. You also want total sums per month,
per product etc.
The best is of course to get all at once in the same result set, since
then you only need to scan the data once. in SQL 2005 you can achieve
this with the proprietary extensions WITH CUBE and WITH ROLLUP to
the GROUP BY clause. SQL 2008, currently in beta, implements GROUPING
SETS from the ANSI standard, and are a lot more powerful. (They are
also a lot more confusing to use.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|