Help with aggregate columns - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with aggregate columns

Author

Topic

bstanley
Starting Member

2 Posts

Posted - 2009-10-15 : 18:32:36

Greetings,

I have two tables Transaction and TransactionDetail. For each row in transaction, the TransactionDetail table has many rows. This is how the tables look like


Transaction Table
TransactionID   ProductID TransactionDate
~~~~~~~~~~~~~   ~~~~~~~~~ ~~~~~~~~~~~~~~~~
    123456789   ABCXYZ    10/09/2009

TransactionDetail table
TransactionDetailID   TransactionID  Amount
~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~  ~~~~~~
                 1    123456789       25
                 2    123456789       50
                 3    123456789       15

I need to write a query that will sum the amounts for each transaction. Both Transaction and TransactionDetail have a million rows or more on them.
I wrote this query.


SELECT
  trans.ProductID ProductID, trans.TransactionDate Date
  (
    select SUM(Amount) 
    from TransactionDetail tdetl
    where tdetl.TransactionID = trans.TransactionID
  ) Total,
FROM
  [Transaction] trans
WHERE
  AND trans.TransactionDate = '10/02/2009'

This however is very slow and take like 10 minutes to complete. Where have I gone wrong? Also note that this is a simplified version of what it really is. There are multiple SUM columns.

Can someone help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 20:14:54

[code]
SELECT
trans.ProductID ProductID, trans.TransactionDate Date
(
select SUM(Amount)
from TransactionDetail tdetl
where tdetl.TransactionID = trans.TransactionID
) Total,,
, t.Total
FROM
[Transaction] trans
CROSS APPLY
(
select SUM(Amount) as Total
from TransactionDetail tdetl
where tdetl.TransactionID = trans.TransactionID
) t
WHERE
~~AND~~ trans.TransactionDate = '10/02/2009'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bstanley
Starting Member

2 Posts

Posted - 2009-10-16 : 12:34:15

Thanks for the answer, but this is not too fast either. Takes about the same time to complete. I am trying different methods now and will post and answer when I find one. Thanks again.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-10-18 : 11:23:21

Do you have index on TransactionId and TransactionDate?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-18 : 12:24:47

[code]
select trans.ProductID ProductID, trans.TransactionDate Date, SUM(tdetl.Amount) as Total
from [Transaction] trans
INNER JOIN TransactionDetail tdetl ON trans.TransactionID = tdetl.TransactionID
where trans.TransactionDate = '10/02/2009'
group by trans.ProductID ProductID, trans.TransactionDate Date
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources