avg of sums - 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)

avg of sums

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-03 : 16:46:23

i have a need to find an average of a sum for particular records.
this is/was my query:

select joitem.fpartno as part,
AVG(Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)/60.00)) as hours
from ladetail 
join prempl on 
ladetail.fempno=prempl.fempno
join joitem on
ladetail.fjobno=joitem.fjobno
where fdate >= '2006-07-01' and fdate <='2007-04-01'
and prempl.fdept='10'
group by joitem.fpartno
order by joitem.fpartno

until this error came back:
Msg 130, Level 15, State 1, Line 2
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

my question is hw do you do this legaly? thanx

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-03 : 16:54:21

Your query doesn't make sense because you are grouping by joitem.fpartno, which will give you one sum per unique part number, and then averaging that without performing higher level grouping, so you'd be getting the average of one value, which is the same value.

Perhaps you mean that you want the sum and the average for each part number? If so then this would be the way to do it.

select joitem.fpartno as part,
Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)/60.00) as TotalHours,
Avg(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)/60.00) as AverageHours
from ladetail 
join prempl on 
ladetail.fempno=prempl.fempno
join joitem on
ladetail.fjobno=joitem.fjobno
where fdate >= '2006-07-01' and fdate <='2007-04-01'
and prempl.fdept='10'
group by joitem.fpartno
order by joitem.fpartno

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-03 : 16:55:42

What would your definition of "average of a sum" be?

Since your query will only return one row for each part, then there is only one sum for the part, so I would say that "average of the sum" equals the sum.

CODO ERGO SUM

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-03 : 17:04:49

:-) never said that the query was making sense yet....
however many thanx for prompt responses.
basically we have certain jobs that we track in our erp db. they are done over and over, but to find out the total number of hours of an instance of a job took, you have to sum up all the hours any given worker has worked on that job. that would be the sum.
what i am trying to accomplish is the get the average for any given job that has been done multiple times... that would be the avg of sums
i hope it makes more sense now. sorry for the confusion

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 17:10:27

1) You want the sum up the time for each worker?
2) All workers belong to an instance?
3) Then you want the average time for all instances within the job?

a) sum up all time for instance directly
b) use the resultset as a derived table and use AVG function on that

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 17:11:34

Or, post some relevant sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 17:26:04

Is this the underlying idea you want?

-- Prepare sample data
DECLARE	@Sample TABLE (Job INT, Instance INT, Worker INT, Hours DECIMAL(6, 4))

INSERT	@Sample
SELECT	1, 1, 1, 2.5 UNION ALL
SELECT	1, 1, 2, 3.0 UNION ALL
SELECT	1, 2, 1, 1.0 UNION ALL
SELECT	1, 2, 3, 2.5 UNION ALL
SELECT	2, 1, 1, 7.5 UNION ALL
SELECT	2, 3, 1, 6.5

-- Show the expected output
SELECT		x.Job,
		AVG(x.Hours) AS Hours
FROM		(
			SELECT		Job, 
					Instance,
					SUM(Hours) AS Hours
			FROM		@Sample
			GROUP BY	Job,
					Instance
		) AS x
GROUP BY	x.Job
ORDER BY	x.Job

Peter Larsson
Helsingborg, Sweden

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-03 : 17:29:48

ok. i'll try to be more detailed...
let's say i have 10 different workers who always work on the same type of job. That job is associated with a part number X.
so in essence i will have the same 10 people work always on the same part number X, but every time they do it the job number will differ.
so what i want is to sum up the TOTAL time worked any given job that was associated with that part number X.
let's say they worked on this part 3 different times:
Job 1 ----- part X ----- 8hrs(total)
Job 2 ----- part X ----- 8hrs(total)
Job 3 ----- part X ----- 5hrs(total)

i want to find the avg for that part X which in this case would be 7hrs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 17:36:40

Did you try out the example posted to you at 17:26:04 ?

Peter Larsson
Helsingborg, Sweden

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-03 : 17:38:49

no sorry. i will in a sec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 17:42:12

[code]-- Prepare sample data
DECLARE @Sample TABLE (Job INT, Instance INT, Worker INT, Hours DECIMAL(6, 4))

INSERT @Sample
SELECT 1, 1, 1, 2.5 UNION ALL
SELECT 1, 1, 2, 3.0 UNION ALL
SELECT 1, 2, 1, 1.0 UNION ALL
SELECT 1, 2, 3, 2.5 UNION ALL
SELECT 2, 1, 1, 7.5 UNION ALL
SELECT 2, 3, 1, 6.5

-- Show the expected output
SELECT x.Instance,
AVG(x.Hours) AS Hours
FROM (
SELECT Job,
Instance,
SUM(Hours) AS Hours
FROM @Sample
GROUP BY Job,
Instance
) AS x
GROUP BY x.Instance
ORDER BY x.Instance[/code]

Peter Larsson
Helsingborg, Sweden

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-04-09 : 09:34:58

i tried it now and it is exactly what i wanted. thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources