how to attach total average to each row...

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

how to attach total average to each row...

Author

Topic

anilr499
Starting Member

18 Posts

Posted - 2012-05-10 : 22:27:19

will some body help me.....

This is my query:

select C_PROJECT,avg(CAST(kpi as decimal(10,2))) as avr from kpitable where KPI is not null
group by c_project

c_project avr
Performance Mgmt & Delivery 1.492753
RACE (Vendor, Contract Mgmt) 1.875000
Portfolio 1.750000
Data Conversion/Integration 1.700000
Functional & Process 2.638888
Post Live Readiness 3.000000
Technical Architecture 2.363636
Business Process Design & BCP 3.250000
Functional, Product & Parameter 2.982456
GL Accounting & Regulatory Reporting 2.750000
Knowledge Management & Training 2.250000
Testing 2.500000
Infrastructure Assessment 2.882352

in the above query am getting the average of kpi depending on c_project....

but now i need another column of average ...

which is the avg of whole kpi...

some thing like this ....

select avg(CAST(kpi as decimal(10,2))) as totavr from kpitable WHERE KPI is not null

but in this am getting only one column and row ...

totavr

2.421188

According to my requirement ...this totavr should repeat for every distinct c_project....

finally my table should be like this.....

c_project avr totavr
Performance Mgmt & Delivery 1.492753 2.421188
RACE (Vendor, Contract Mgmt) 1.875000 2.421188
Portfolio 1.750000 2.421188
Data Conversion/Integration 1.700000 2.421188
Functional & Process 2.638888 2.421188
Post Live Readiness 3.000000 2.421188
Technical Architecture 2.363636 2.421188
Business Process Design & BCP 3.250000 2.421188
Functional, Product & Parameter 2.982456 2.421188
GL Accounting & Regulatory Reporting 2.750000 2.421188
Knowledge Management & Training 2.250000 2.421188
Testing 2.500000 2.421188
Infrastructure Assessment 2.882352 2.421188

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 22:31:18

[code]
select C_PROJECT,avg(CAST(kpi as decimal(10,2))) as avr ,Totavr
from
(
select *,avg(CAST(kpi as decimal(10,2))) over () as Totavr
from kpitable
where KPI is not null
)t
group by c_project,Totavr
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anilr499
Starting Member

18 Posts

Posted - 2012-05-10 : 22:43:32

quote:
Originally posted by visakh16

select C_PROJECT,avg(CAST(kpi as decimal(10,2))) as avr ,Totavr
from
(
select *,avg(CAST(kpi as decimal(10,2))) over () as Totavr
from kpitable
where KPI is not null
)t
group by c_project,Totavr

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
thank you i got it....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 22:48:43

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources