sum then join ? - 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 2000 Forums

SQL Server Development (2000)

sum then join ?

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-06-03 : 12:39:43

SELECT Customer, SUM(Elapsedsecs) AS SystemTime
FROM dbo.TestBlockStatus
WHERE (SystemName = 'foo') AND (TimeEnd >= '04/12/2005') AND ('05/12/2005' >= TimeEnd)
GROUP BY Customer
ORDER BY SystemTime

RESULT
xyz 250

this nicely sums the Elapsedsecs, but I'd like to JOIN ? the minimal TimeStart of each Customer. ( I have a field TimeStart )

Customer TimeStart ElapsedSecs
xyz dt1 100
xyz dt2 150

RESULT
xyz dt1 250

But Joins usually refer to an exiting table so being a novice I am confused. Thanks

andrewcw

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 12:44:04

quote:
Originally posted by andrewcw
But Joins usually refer to an exiting table so being a novice I am confused. Thanks

You're not the only one.

Follow the directions in the link below.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-06-03 : 13:03:16

OK Maybe I was not clear
I have a table like this:
Customer BlkNum StartTime ElapsedTime
xyz 100 10:40:30 150
xyz 200 10:60:20 100

SELECT Customer, SUM(Elapsedsecs) AS SystemTime
FROM dbo.TestBlockStatus
GROUP BY Customer
ORDER BY SystemTime

I get a Result Like this
xyz 250

BUT... I'd like to have an additonal field where the new field is the first StartTime of the process
with a recordset that looks like
xyz 250 10:40:30

I can imagine a query that would give me each customer and the first StartTime, but how do I 'join' the 2 resultset by the customer field. Is this still vague ? Thanks

andrewcw

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-03 : 13:50:19

Do you just want to return the earliest (min) startTime as the additional column?

**Also, in future postings please use syntax as follows (as detailed in the link posted above)...

declare @testBlockStatus table (customer char(3), blkNum int, startTime datetime, elapsedSecs int)
insert into @testBlockStatus
select 'xyz',100,'2005-06-03 10:30:30.000',150 union
select 'xyz',200,'2005-06-03 10:50:20.000',100

select
	customer, 
	sum(elapsedSecs) as systemtime,
	min(startTime) as firstStartTime
from @testBlockStatus
group by customer
order by systemTime

andrewcw
Posting Yak Master

133 Posts

Posted - 2005-06-03 : 13:54:27

Wow VERY sharp, very clear. Thanks !!!

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources