Query to collapse mutliple rows

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)

Query to collapse mutliple rows

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-05-21 : 15:40:55

Hello -

I am having a difficult time trying to generate a result set that collapses multiple rows into one. However, I am also getting some of the columns that I am trying to SUM, to double their value.

I have two tables as:

Table 1
EmpID (int), AcctID (int), ActivityDate(datetime), Process (nvarchar 20), Cost (money)

Table 2
EmpID (int), AcctID (int), ActivityDate(datetime), Process (nvarchar 20), Duration (smallint)

Some sample data:
Table 1
501, 7998, 04-2-2008, Process1, $100
501, 7998, 04-2-2008, Process2, $75

Table 2
501, 7998, 04-2-2008, Process2, 3

Result i need is:
501, 7998, 04-02-2008, $175, 3

What I am getting is:
501, 7998, 04-02-2008, $175, 6

Any suggestions or help will be appreciated.

Thank you
- will

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 15:48:23

Try this:

SELECT t2.EmpID, t2.AcctID, t2.ActivityDate, dt.CostSum, t2.Duration
FROM Table2 t2
INNER JOIN (SELECT EmpID, AcctID, ActivityDate, SUM(Cost) AS CostSum FROM Table1 GROUP BY EmpID, AcctID, ActivityDate) dt
ON t2.EmpId = dt.EmpId AND t2.AcctID = dt.AcctID AND t2.ActivityDate = t2.ActivityDate

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-05-22 : 01:11:45

Thanks...that works great.
- will

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2008-05-22 : 01:14:04

Actually, sorry. This does work great. However, I have discovered another issue with the data that may require something different. I am going to try and experiment with the t-sql you gave me and if that doesn't work I'll post the changes/issues I am facing.

thanks again,
- will

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources