try below query..replace @table with your table name & check..
declare @kpitable table
(
id int identity(1,1),c_project varchar(100)
)
insert into @kpitable
select 'Performance Mgmt & Delivery' union all
select 'Performance Mgmt & Delivery' union all
select 'Performance Mgmt & Delivery' union all
select 'Performance Mgmt & Delivery' union all
select 'Performance Mgmt & Delivery' union all
select 'Performance Mgmt & Delivery' union all
select 'RACE (Vendor, Contract Mgmt)' union all
select 'RACE (Vendor, Contract Mgmt)' union all
select 'RACE (Vendor, Contract Mgmt)' union all
select 'RACE (Vendor, Contract Mgmt)' union all
select 'RACE (Vendor, Contract Mgmt)'
;with cte as
(
select c_project,COUNT(c_project) as cnt
from @kpitable
GROUP BY c_project
),
cte1 as
(
select c_project,SUM(cnt)*100.0 as avgdata
from cte
GROUP BY c_project
)
select cte1.c_project,cast(cnt/avgdata as decimal(18,2)) as totalavg
from cte1
inner join cte on cte1.c_project=cte.c_project
SELECT c_project, cnt, (cnt/SUM([ProjectWiseSUM]))*100 AS Average FROM
(
SELECT c_project, count(c_project) AS cnt, sum(cnt) AS [ProjectWiseSUM] FROM table GROUP BY c_project
) x
Select Project,CNT,(CNT/(select COUNT(C_PROJECT) from kpitable group by c_project))*100 from (select c_project as Project,COUNT(C_PROJECT) AS CNT from kpitable group by c_project) as tblTemp
anilr499
Member
94 Points
389 Posts
suggest me a query
May 10, 2012 05:56 AM|LINK
suggest me...
i need one more column with average.....
some thing like avg=cnt/sum(cnt)*100)....but thats not wrking....
query:
select c_project,COUNT(C_PROJECT) AS CNT from kpitable group by c_project
table:
c_project cnt
Performance Mgmt & Delivery 79
RACE (Vendor, Contract Mgmt) 39
Portfolio 4
Data Conversion/Integration 10
- Functional & Process 83
Post Live Readiness 65
Technical Architecture 58
Business Process Design & BCP 15
Functional, Product & Parameter 83
Accounting & Regulatory Reporting 93
Knowledge Management & Training 26
Testing 42
Infrastructure Assessment 45
thanks...
yrb.yogi
Star
14460 Points
2402 Posts
Re: suggest me a query
May 10, 2012 06:06 AM|LINK
try below query..replace @table with your table name & check..
.Net All About
anilr499
Member
94 Points
389 Posts
Re: suggest me a query
May 10, 2012 06:27 AM|LINK
hi yogi...
am not getting average....
my table should look like this...
avg=(cnt/sum(cnt))*100 here sum(cnt)=642 ....i need like this.....
more over i cant insert into the "c_project" table it may varyyy.....
c_project cnt avg
Performance Mgmt & Delivery 79 (79/(sum(cnt))*100)
RACE (Vendor, Contract Mgmt) 39
Portfolio 4
Data Conversion/Integration 10
- Functional & Process 83
Post Live Readiness 65
Technical Architecture 58
Business Process Design & BCP 15
Functional, Product & Parameter 83
Accounting & Regulatory Reporting 93
Knowledge Management & Training 26
Testing 42
thank you..............
yrb.yogi
Star
14460 Points
2402 Posts
Re: suggest me a query
May 10, 2012 06:55 AM|LINK
How do you get 642?
cnt is column present in table? Can you show us your table structure with some data..
.Net All About
shivv
Participant
1566 Points
283 Posts
Re: suggest me a query
May 10, 2012 07:11 AM|LINK
Check this:
sarath.cs.81...
Member
4 Points
4 Posts
Re: suggest me a query
May 10, 2012 07:17 AM|LINK
Hi,
Try this
Select Project,CNT,(CNT/(select COUNT(C_PROJECT) from kpitable group by c_project))*100 from (select c_project as Project,COUNT(C_PROJECT) AS CNT from kpitable group by c_project) as tblTemp
Thanks & Regards,
Sarath
anilr499
Member
94 Points
389 Posts
Re: suggest me a query
May 10, 2012 07:19 AM|LINK
hi
by adding all the numbers in cnt column i get sum(cnt)=642
c_project cnt avg
Performance Mgmt & Delivery 79 79/642
RACE (Vendor, Contract Mgmt) 39 39/642
Portfolio 4 4/642
Data Conversion/Integration 10 10/642
- Functional & Process 83 83/642
Post Live Readiness 65 65/642
Technical Architecture 58 58/642
Business Process Design & BCP 15 15/642
Functional, Product & Parameter 83 83/642
Accounting & Regulatory Reporting 93 93/642
Knowledge Management & Training 26 26/642
Testing 42 42/642
Infrastructure Assessment 45 45/642
thank you...
yrb.yogi
Star
14460 Points
2402 Posts
Re: suggest me a query
May 10, 2012 07:38 AM|LINK
declare @kpitable table ( c_project varchar(100),cnt int ) insert into @kpitable select 'Performance Mgmt & Delivery',79 union all select 'RACE (Vendor, Contract Mgmt)',39 union all select 'Portfolio',4 union all select 'Data Conversion/Integration',10 union all select '- Functional & Process',83 union all select 'Post Live Readiness',65 union all select 'Technical Architecture',58 union all select 'Business Process Design & BCP',15 union all select 'Functional, Product & Parameter',8 union all select 'Accounting & Regulatory Reporting',2 union all select 'Knowledge Management & Training',2 union all select 'Testing',42 union all select 'Infrastructure Assessment',45 ;with cte as ( select c_project,cnt,SUM(cnt)OVER() as totalsum from @kpitable ) select c_project,cnt,totalsum, cnt/CAST(totalsum AS DECIMAL(10,6)), --division cnt/CAST(totalsum AS DECIMAL(10,6))*100 --avg data from cte --output --c_project cnt totalsum (No column name) (No column name) --Performance Mgmt & Delivery 79 452 0.17477876106 17.47787610600 --RACE (Vendor, Contract Mgmt) 39 452 0.08628318584 8.62831858400 --Portfolio 4 452 0.00884955752 0.88495575200 --Data Conversion/Integration 10 452 0.02212389380 2.21238938000 --- Functional & Process 83 452 0.18362831858 18.36283185800 --Post Live Readiness 65 452 0.14380530973 14.38053097300 --Technical Architecture 58 452 0.12831858407 12.83185840700 --Business Process Design & BCP 15 452 0.03318584070 3.31858407000 --Functional, Product & Parameter 8 452 0.01769911504 1.76991150400 --Accounting & Regulatory Reporting 2 452 0.00442477876 0.44247787600 --Knowledge Management & Training 2 452 0.00442477876 0.44247787600 --Testing 42 452 0.09292035398 9.29203539800 --Infrastructure Assessment 45 452 0.09955752212 9.95575221200.Net All About
anilr499
Member
94 Points
389 Posts
Re: suggest me a query
May 10, 2012 07:41 AM|LINK
hi shiv
its not wrking...
i need avg of cnt column....
some thing like in my avg column evry row should calculate (cnt/sum(cnt))...
for example in my tablae take first row...
c_project cnt avg
Performance Mgmt & Delivery 79 79/642
the result of 79/642 should be my avg colmn value....
642 is nothing but the sum of all the values in cnt column...
thank you...
yrb.yogi
Star
14460 Points
2402 Posts
Re: suggest me a query
May 10, 2012 08:02 AM|LINK
whats not working..?
I have shown you working solutions as per your requirement..
In that query, we are doing sum of all cnt columns and than deviding cnt column to total sum & then after multiplieng it to 100..
So whats is wrong?
can you highlighted the wrong results here?
.Net All About