SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Counting Parents and Children with Count Distinct
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/10/2006 :  07:46:16  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 01/13/2006 :  04:17:39  Show Profile  Reply with Quote
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


Edited by - Arnold Fribble on 01/13/2006 04:48:56
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 01/13/2006 :  05:24:07  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.42 seconds. Powered By: Snitz Forums 2000