Best way to get aggregates over resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best way to get aggregates over resultset

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-19 : 18:40:18

Hi!
What is the best way (performance wise) to get aggregate values i.e. COUNT DISTINCT, over a resultset?
For example, on a Customers resultset:
SELECT O.OrderDate,C.CustName,Fld3,Fld4,Fld5,Fld6,Fld7,Fld8 FROM Orders O INNER JOIN Customers C ON O.CustID = C.CustID AND O.OrderDate BETWEEN '3/1/2001' AND '3/8/2001' And C.Name IN('Smith','Doe','Johnson','Jackson') ORDER BY OrderDate,CustName
and I want also the Count(distinct Orderdate) and Count(distinct CustName)?
1. A Subquery for each which repeats the entire JOIN clause.
2. Group By over all fields
3. Any other suggestions? I'm all ears.

Thanks!

Sarah Berger MCSD

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-20 : 07:16:11

Would the use of the 'with rollup' keywords be of use? Would it achieve all three objectives in one go?

the tradeoff you have to decide is whether or not 3 passes of the database (with the possible benefit of cached data) would be faster than 1 pass of the database which includes extra work to produce the summmary totals that a "rollup" command produces.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-20 : 08:17:27

ROLLUP wouldn't work here: it cannot compute distinct aggregates.
I think you will get 3 passes whichever way you do it since COUNT(DISTINCT) will insert them too. For example, look at the plan for


SELECT CustomerID, COUNT(DISTINCT EmployeeID), COUNT(DISTINCT ShipVia), COUNT(*)
FROM Northwind.dbo.Orders
GROUP BY CustomerID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources