SUGGEST ME A QUERY.... - 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

General SQL Server Forums

New to SQL Server Programming

SUGGEST ME A QUERY....

Author

Topic

anilr499
Starting Member

18 Posts

Posted - 2012-05-10 : 05:43:20

THIS IS MY TABLE:

c_project kpi

Infrastructure Assessment 3
Infrastructure Assessment 3
Infrastructure Assessment 3
Infrastructure Assessment 3
Infrastructure Assessment 3
Infrastructure Assessment 3
Infrastructure Assessment 5
Portfolio 1
Portfolio 4
Portfolio 1
Data Conversion/Integration 1
Data Conversion/Integration 1
Data Conversion/Integration 2
Data Conversion/Integration 2
Data Conversion/Integration 2
Performance Mgmt & Delivery NULL
Performance Mgmt & Delivery NULL
Performance Mgmt & Delivery NULL
Performance Mgmt & Delivery 1
Performance Mgmt & Delivery 1
Performance Mgmt & Delivery 1

I NEED AVG(KPI)...WITH DISTINCT C_PROJECT
HELP ME ...
THANK YOU...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-10 : 05:45:46

what query ? What do you need ?

KH
[spoiler]Time is always against us[/spoiler]

anilr499
Starting Member

18 Posts

Posted - 2012-05-10 : 05:52:08

quote:
Originally posted by khtan

what query ? What do you need ?

KH
[spoiler]Time is always against us[/spoiler]

THAT IS MY TABLE NAME "ANI"....
I NEED TO GET THE AVG OF KPI WITH DISTINCT C_PROJECT NAAMES...

SOME THING LIKE
EG:
COLUMN1 |||||||||||||||||||||| COLUMN2
C_PROJECT||||||||||||||||||||||| AVG (KPI)

Infrastructure Assessment||||||||||||||||||| SUM(KPI)/COUNT(KPI)
Portfolio ||||||||||||||||||| SUM(KPI)/COUNT(KPI)
Data Conversion/Integration ||||||||||||||||||| SUM(KPI)/COUNT(KPI)

HOW CAN I GET....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-10 : 06:18:29

[code]
SELECT c_project, avg(kpi * 1.0)
from yourtable
group by c_project
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-10 : 18:55:19



This will give you correct answer

SELECT c_project, avg(isnull(kpi,0)*1.0)
from   yourtable
group by c_project

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-14 : 05:53:21

[code]
--Creating Table

Create Table Ex
(c_project varchar(30),
kpi int)

--Inserting Sample Data

Insert into Ex
Select 'Infrastructure Assessment', 3
UNION ALL
Select 'Infrastructure Assessment', 3
UNION ALL
Select 'Infrastructure Assessment', 3
UNION ALL
Select 'Infrastructure Assessment', 3
UNION ALL
Select 'Infrastructure Assessment', 3
UNION ALL
Select 'Infrastructure Assessment', 3
UNION ALL
Select 'Infrastructure Assessment', 5
UNION ALL
Select 'Portfolio', 1
UNION ALL
Select 'Portfolio', 4
UNION ALL
Select 'Portfolio', 1
UNION ALL
Select 'Data Conversion/Integration', 1
UNION ALL
Select 'Data Conversion/Integration', 1
UNION ALL
Select 'Data Conversion/Integration', 2
UNION ALL
Select 'Data Conversion/Integration', 2
UNION ALL
Select 'Data Conversion/Integration', 2
UNION ALL
Select 'Performance Mgmt & Delivery', NULL
UNION ALL
Select 'Performance Mgmt & Delivery', NULL
UNION ALL
Select 'Performance Mgmt & Delivery', NULL
UNION ALL
Select 'Performance Mgmt & Delivery', 1
UNION ALL
Select 'Performance Mgmt & Delivery', 1
UNION ALL
Select 'Performance Mgmt & Delivery', 1

--Query For Your Requirement

Select c_project, AVG(Cast(kpi As Float)) as Average_kpi From Ex
Group By c_project
[/code]

Hope it helps.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources