aggregate function on aggregate funtion

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

General SQL Server Forums

New to SQL Server Programming

aggregate function on aggregate funtion

Author

Topic

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-06-28 : 12:31:45

I am trying to do this query:

SELECT sum(datediff(s, min(le.event_time), max(le.event_time))
FROM log_event le INNER JOIN equipment e ON le.equipment_id = e.equipment_id

basically what i am trying to accomplish is this. I have a table that has a bunch of dates and times in which an equipment performs something. Each equipment has a connect time and a disconnect time and i want to see how long all of the equipment have connected.

example:

equip 1 - connect; event_time = '6/15/2009 12:50'
equip 1 - random message; event_time = '6/15/2009 12:52'
equip 1 - disconnect; event_time = '6/15/2009 12:55'

total connect time = 5 minutes

equip 2 - connect; event_time = '6/16/2009 12:45'
equip 2 - disconnect; event_time = '6/16/2009 12:55'

total connect time = 10 minutes

so i want my query to return 15 minutes because thats the total connect time if you add up both equipment

the problem is that i get this message:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

i understand completely what this message means but i don't see a way around this. Any suggestions?

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 12:54:26

plese post the DDL

But if log_event is defined like

id, event_time

Tell me how you relate the rows to each other?

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

Add yourself!
http://www.frappr.com/sqlteam

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-06-28 : 12:57:27

ok so i have one table log_event with columns
log_event_id; event_time; equipment_id

another table equipment with columns
equipment_id; name

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-28 : 13:05:24

Something like this should work:

SELECT
	SUM(datediff(MINUTE, MinTime, MaxTime)) AS TotalMinutes
FROM
(
	SELECT
		min(le.event_time) AS MinTime, 
		max(le.event_time) AS MaxTime
	FROM 
		log_event 
	GROUP BY 
		equipment_id
)

unless you need to have the MIN a Connect event and the MAX be a disconnect event. Then that changes thing s a little.

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-06-28 : 13:12:42

With that, i get

"Incorrect syntax near ')'."

but i think that it may work otherwise. I will look into it too but just in case i don't see it, i wanted to reply

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 13:18:58

no, it's got to be like shift work....

start...stop...start..stop

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

Add yourself!
http://www.frappr.com/sqlteam

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-06-28 : 13:34:32

sorry, im kind of embarrassed to ask but i don't understand what you mean by that

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 13:38:01

post some sample data

or answer this

when do you record rows in the even_log

When a machine starts...you eneter a row
when a machine stops you eneter a row
When a machine starts...you eneter a row
when a machine stops you eneter a row
When a machine starts...you eneter a row
when a machine stops you eneter a row
When a machine starts...you eneter a row
when a machine stops you eneter a row

is my guess

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

Add yourself!
http://www.frappr.com/sqlteam

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-06-28 : 13:52:17

yes you are correct in that logic just one add on that doesnt even matter. row is added for a connect, for any activity and for a disconnect. So i test this and it does work:

SELECT
min(event_time) AS MinTime,
max(event_time) AS MaxTime
FROM
log_event
group by
equipment_id

but when i put it all together:

SELECT
SUM(datediff(s, MinTime, MaxTime))
FROM
(
SELECT
min(event_time) AS MinTime,
max(event_time) AS MaxTime
FROM
log_event
group by
equipment_id
)

i get the following error:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 13:55:35

[code]
SELECT
SUM(datediff(s, MinTime, MaxTime))
FROM
(
SELECT
min(event_time) AS MinTime,
max(event_time) AS MaxTime
FROM
log_event
group by
equipment_id
)T
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-06-28 : 13:58:57

It works!

thank you so much

i was wondering though, what is that T for?

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 14:22:01

Have a look here

http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 15:45:58

whatdya mean it works?



/*

equip 1 - connect; event_time = '6/15/2009 12:50'
equip 1 - random message; event_time = '6/15/2009 12:52'
equip 1 - disconnect; event_time = '6/15/2009 12:55'
total connect time = 5 minutes

equip 1 - connect; event_time = '6/15/2009 18:50'
equip 1 - random message; event_time = '6/15/2009 18:52'
equip 1 - disconnect; event_time = '6/15/2009 18:55'
total connect time = 5 minutes



equip 2 - connect; event_time = '6/16/2009 12:45'
equip 2 - disconnect; event_time = '6/16/2009 12:55'
total connect time = 10 minutes

equip 2 - connect; event_time = '6/16/2009 20:45'
equip 2 - disconnect; event_time = '6/16/2009 20:55'
total connect time = 10 minutes


What you asked:			so i want my query to return 15 minutes because thats the total connect 
						time if you add up both equipment

What you really asked:	so i want the total connect time if you add up both equipment

*/


CREATE TABLE #myTable99 
	(log_event_id int IDENTITY(1,1), event_time datetime, equipment_id int, event_message varchar(2000))
GO

INSERT INTO #myTable99(event_time, equipment_id, event_message)
SELECT '6/15/2009 12:50', 1, 'Start'	UNION ALL
SELECT '6/15/2009 12:52', 1, 'Random'	UNION ALL
SELECT '6/15/2009 12:55', 1, 'Stop'		UNION ALL
SELECT '6/15/2009 18:50', 1, 'Start'	UNION ALL
SELECT '6/15/2009 18:52', 1, 'Random'	UNION ALL
SELECT '6/15/2009 18:55', 1, 'Stop'		UNION ALL
SELECT '6/16/2009 12:45', 2, 'Start'	UNION ALL
SELECT '6/16/2009 12:55', 2, 'Stop'		UNION ALL
SELECT '6/16/2010 20:45', 2, 'Start'	UNION ALL
SELECT '6/16/2010 20:55', 2, 'Stop'
GO

SELECT * FROM #myTable99
GO

SELECT
SUM(datediff(mi, MinTime, MaxTime))
FROM
(
SELECT
min(event_time) AS MinTime,
max(event_time) AS MaxTime
FROM
#myTable99
group by
equipment_id
)T

DROP TABLE #myTable99
GO

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

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources