Need help with aggregate subquery

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)

Need help with aggregate subquery

Author

Topic

jrun777
Starting Member

2 Posts

Posted - 2009-09-04 : 15:20:08

I have a query which uses a SUM function to add together REGULAR hours and OVERTIME hours for each pay period for an employee. The code below achieves this and produces a table grouped by pay period with a column showing the total hours (sum of regular and overtime for each period).

What I want to do now is get the AVERAGE of the total hours (sum_csecurrhrs). The SUM function would be a subquery with the outer query being the one to calculate the average. But I can't figure out how to reference the calculated field sum_csecurrhrs from below to be able to average it. Any ideas?

Here is my code:

SELECT ebclock,csudateend,sum(csecurrhrs) as sum_hours
FROM vtchecksummHE, vtchecksumm, vtebase
where vtchecksummHE.[cseflxidcsu] = vtchecksumm.[csuflxid]
AND vtchecksumm.[csuflxideb] = vtebase.[ebflxid]
AND [csudateend] BETWEEN '#STARTDATE#' AND '#ENDDATE#'
AND [csepaytypelong] IN ('REGULAR','OVERTIME')
AND ebclock = '207216'
group by ebclock, csudateend

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-05 : 20:36:38

Is this what you're looking for?



select avg(sum_hours) as average_hours from 
(SELECT ebclock,csudateend,sum(csecurrhrs) as sum_hours
	FROM vtchecksummHE
	join vtchecksumm on vtchecksummHE.[cseflxidcsu] = vtchecksumm.[csuflxid]
	join vtebase on vtchecksumm.[csuflxideb] = vtebase.[ebflxid]
	where [csudateend] BETWEEN '#STARTDATE#' AND '#ENDDATE#'
		AND [csepaytypelong] IN ('REGULAR','OVERTIME')
		AND ebclock = '207216'
	group by ebclock, csudateend
)

An infinite universe is the ultimate cartesian product.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-06 : 13:55:21

quote:

Originally posted by cat_jesus

Is this what you're looking for?



select avg(sum_hours) as average_hours from 
(SELECT ebclock,csudateend,sum(csecurrhrs) as sum_hours
	FROM vtchecksummHE
	join vtchecksumm on vtchecksummHE.[cseflxidcsu] = vtchecksumm.[csuflxid]
	join vtebase on vtchecksumm.[csuflxideb] = vtebase.[ebflxid]
	where [csudateend] BETWEEN '#STARTDATE#' AND '#ENDDATE#'
		AND [csepaytypelong] IN ('REGULAR','OVERTIME')
		AND ebclock = '207216'
	group by ebclock, csudateend
)t

An infinite universe is the ultimate cartesian product.

remember to give table alias

jrun777
Starting Member

2 Posts

Posted - 2009-09-08 : 11:16:32

Thank you both very much for your help on this. I was able to get it to work now. What I had failed to do in my attempts was reference the aliased field name "sum_hours" directly in the avg() function. Instead of using avg(sum_hours) I had been using avg(csecurrhrs). Simple mistake but sometimes it just helps to have another set of eyes look at it. Thanks again for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources