SUM() on a subquery result - 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

Transact-SQL (2000)

SUM() on a subquery result

Author

Topic

kirigoe
Starting Member

2 Posts

Posted - 2006-05-18 : 08:34:40

Hi,

I'm writing a query to get the number of seconds our Indesign-to-PDF generator software uses for each of our clients. The query runs fine but I want to apply a SUM() to the number of seconds and GROUP BY customers to find out how much time each customers uses. I've tried to apply the appropriate functions but with no luck.

The query result now looks like this:

customer runtime
------------------
ABC 100
ABC 150
DEF 300
DEF 500

SELECT LEFT(RQ.Documents, 3) AS customer,
	DATEDIFF(second, (
		SELECT EventTS
		FROM RequestEvents 
		WHERE EventName = 'STARTED' 
		AND RequestId = RE.RequestId)
		, EventTS) AS runtime

FROM RequestEvents RE
INNER JOIN RequestQueue RQ ON RQ.RequestId = RE.RequestId
WHERE EventName = 'COMPLETED'
AND EventInfo = 'Successful processing!!'
ORDER BY runtime DESC

I hope this is enough to understand the problem, if not, I can post DDL's too.

Thanks in advance!
Daniel

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-18 : 09:09:22

[code]
select customer,sum(runtime) as [runtime]
from (
SELECT LEFT(RQ.Documents, 3) AS customer,
DATEDIFF(second, (
SELECT EventTS
FROM RequestEvents
WHERE EventName = 'STARTED'
AND RequestId = RE.RequestId)
, EventTS) AS runtime

FROM RequestEvents RE
INNER JOIN RequestQueue RQ ON RQ.RequestId = RE.RequestId
WHERE EventName = 'COMPLETED'
AND EventInfo = 'Successful processing!!'
) s
group by customer

[/code]

--------------------
keeping it simple...

kirigoe
Starting Member

2 Posts

Posted - 2006-05-18 : 10:04:48

thank you very much, this works splendidly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources