Caculating problems - 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

Development Tools

Reporting Services Development

Caculating problems

Author

Topic

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2007-11-12 : 15:47:56

Hi helpers,
I wonder how to create a caculating field within a group row.
For example:
a table with detailed section and a group section.
detailed section: hours, dollars
group section: Avg(hours), Avg(dollars), Avg(rate)??

Is any way I can caculate Avg(rate) field based on Avg(dollars)/Avg(hours) instead of using sum(dollars)/sum(hours)???

Thanks in advance!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 16:37:35

Umm,

you answered your own question.

If average rate is based on hours/dollars you could create the column and avg the resulting column.

It is not particular clear what you are asking though, but you would likely have to use correlated subqueries or derived tables to return the desired results.

Poor planning on your part does not constitute an emergency on my part.

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2007-11-12 : 17:11:25

Well, that's just a simple example.
I have a situation that the fields of hours & dollars were converted to diff numbers and my Avg(rate) needs to base on those new Avg fields.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 17:24:58

You are going to have to lay out some sample data and desired results.

Simple math is just that, simple math.

Not sure what you are looking for but here are same basic examples. Best I can do for you without sample structure/data/example/desired results..


Create Table #foo (SampleID tinyint not null,Hours int not null, Dollars numeric(12,2) not null)

Insert INto #foo (SampleID,Hours, Dollars)

Select 1,10,100.50 UNION ALL
Select 1,7,100.50 UNION ALL
Select 1,15,100.50 UNION ALL
Select 1,17,100.50 UNION ALL
Select 2,11,110.50 UNION ALL
Select 2,12,110.50 UNION ALL
Select 2,19,110.50 UNION ALL
Select 2,9,110.50 UNION ALL
Select 2,12,110.50 UNION ALL
Select 2,14,110.50 UNION ALL
Select 2,12,110.50 UNION ALL
Select 3,22,150.00 UNION ALL
Select 3,16,150.00 UNION ALL
Select 3,12,150.00 




Select SampleID,Avg(Hours) as Avg_Hours, Avg(Dollars) as Avg_Dollars, Avg(dollars/hours) as Avg_Rate
FROM #foo
Group by SampleID



--or

Select SampleID,Avg(Avg_Dollars)/Avg(Avg_Hours) as Avg_Rate
FROM  (Select SampleID,Avg(Hours) as Avg_Hours, Avg(Dollars) as Avg_Dollars, Avg(dollars/hours) as Avg_Rate
		FROM #foo
		Group by SampleID) a
Group by SampleID



Drop Table #foo

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 17:34:54

Incidentally, I wasnt paying attention that this in the reporting services thread...not sure if what I proposed would apply

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources