Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    19

    Unanswered: Aggregate Counting

    Hi,

    How can I aggregate a top 5 count across two satellite tables?

    e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.

    Any help or pointers would be a great help!

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    What's the actual problem with doing this?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use subquerys:
    Code:
    select	Customers.CustomerID,
    	isnull(OrdersSubquery.OrderCount, 0) as Orders,
    	isnull(DownloadsSubquery.DownloadCount, 0) as Downloads,
    	isnull(OrdersSubquery.OrderCount, 0) + isnull(DownloadsSubquery.DownloadCount, 0) as Total
    from	Customers
    	left outer join
    		(select	CustomerID,
    			count(*) as OrderCount
    		from	Orders
    		group by CustomerID) OrdersSubquery
    		on Customers.CustomerID = OrdersSubquery.CustomerID
    	left outer join
    		(select	CustomerID,
    			count(*) as DownloadCount
    		from	Downloads
    		group by CustomerID) DownloadsSubquery
    		on Customers.CustomerID = DownloadsSubquery.CustomerID
    ...or count distinct, if you have pkeys on Orders and Downloads:
    Code:
    select	Customers.CustomerID,
    	count(distinct Orders.OrderID) as Orders,
    	count(distinct DownloadsOrderID) as Downloads,
    	count(distinct Orders.OrderID) + count(distinct DownloadsOrderID) as Total
    from	Customers
    	left outer join Orders on Customers.CustomerID = Orders.CustomerID
    	left outer join Downloads on Customers.CustomerID = Downloads.CustomerID
    group by Customers.CustomerID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2004
    Posts
    19
    Thanks for the advice, the top selection was ideal for my purposes!

    I just had to 'customise' it a little and add a few more table counts etc and all's well


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •