Article: Counting Parents and Children with Count Distinct

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

Site Related Forums

Article Discussion

Article: Counting Parents and Children with Count Distinct

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-10 : 07:46:16

The aggregate functions in SQL Server (min, max, sum, count, average, etc.) are great tools for reporting and business analysis. But sometimes, you need to tweak them just a little bit to get exactly the results you need. For example, if your manager came to you and asked for a report on how many sales have been made to your clients and how large they were, would you know how to get the data you need efficiently? Mark ran into something like this recently and here's the approach he took to solve the problem.

Article Link.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-13 : 04:17:39

I realise that this was very much an introductory article, but the problem with doing it that way is that, while it's simple and clean, it isn't particularly efficient.

When mixing DISTINCT aggregate functions with other aggregate functions the execution plan will end up doing each DISTINCT aggregation separately from the other aggregations (probably using a temporary spool table) and then rejoin the results.

Where it's possible, an alternative that's usually more efficient is to partially aggregate by hand.

In this case:


SELECT
  CustomerName,
  COUNT(*) AS OrderCount,
  SUM(LineCount) AS LineCount,
  SUM(OrderAmount) AS TotalAmount
FROM (
    SELECT 
    	H.CustomerName, 
    	COUNT(D.DetailID) as LineCount,
    	SUM(D.LineAmount) as OrderAmount
    FROM
    	OrderHeader H
    	JOIN OrderDetail D ON H.OrderID = D.OrderID
    GROUP BY
    	H.CustomerName, H.OrderId
  ) AS A
GROUP BY CustomerName

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-01-13 : 05:24:07

quote:
Originally posted by Arnold Fribble

I realise that this was very much an introductory article, but the problem with doing it that way is that, while it's simple and clean, it isn't particularly efficient.

And that is one of my pet peeves about SQL DBMS's.
The simpliest (ie most direct) solution is usually the slowest.

I liked it Arnold. Good format and flowed nicely. Although I would have liked to seen a little ERD.. pictures speak a thousand words.

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources