Select sum and divide. Urgent - 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

Select sum and divide. Urgent

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-01-06 : 22:01:47

HI Everyone,

I want to select sum and then divide the by count as below.

col1 col2 col3 col4 .........................
______________________________________________
1 4 empty 5
2 empty 1 5
4 5 empty 5
5 2 2 empty
___________________________________________
(12)/4 = 3.0 (11/3)=3.67 (3)/2=2.50 (15)/3 = 5.0

The col1, col2, col3, col4 ....... is in nvachar type
empty mean nothing inside

jp2code
Posting Yak Master

175 Posts

Posted - 2009-01-06 : 22:03:58

I'm not so good at SQL as others on here, so I'll just say, "look into the 'cast' keyword." If I tried to tell you how to use it, I would probably be telling you wrong.

Avoid Sears Home Improvement

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-01-06 : 22:10:31

i tried this but incorrect.

SELECT
ROUND(SUM(CAST(ISNULL(Col1, 0) AS float)) / COUNT(*), 2) AS [Total Salary],
ROUND(SUM(CAST(ISNULL(Col2, 0) AS float)) / COUNT(*), 2) AS [Total Salary2],
ROUND(SUM(CAST(ISNULL(Col3, 0) AS float)) / COUNT(*), 2) AS [Total Salary3],
ROUND(SUM(CAST(ISNULL(Col4, 0) AS float)) / COUNT(*), 2) AS [Total Salary4]

FROM table
WHERE
(Col1 <> '') and
(Col2 <> '') and
(Col3 <> '') and
(Col4 <> '')

anyone can help in this?

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-01-06 : 22:21:14

SELECT
ROUND(SUM(CAST(ISNULL(Col1, 0) AS float)) / COUNT(*), 2) AS [Total Salary],

FROM table
WHERE
(Col1 <> '')

This is correct but only one by one colomn, i have about 80 column.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-06 : 22:43:30

select sum(cast(coalesce(col1,0))as numeric(18,2))/count(col1) as Totalsalary from urtable

Jai Krishna

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-01-07 : 01:20:31

quote:
Originally posted by Jai Krishna

select sum(cast(coalesce(col1,0))as numeric(18,2))/count(col1) as Totalsalary from urtable

Jai Krishna

but how about another col?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-07 : 02:17:17

select sum(CONVERT(numeric(18,2),ISNULL(col1,0) ))/count(col1),sum(convert(numeric(18,2),ISNULL(col2,0)))/count(col2),....... from urtable

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-07 : 02:24:00

quote:
Originally posted by calvinkwoo3000

quote:
Originally posted by Jai Krishna

select sum(cast(coalesce(col1,0))as numeric(18,2))/count(col1) as Totalsalary from urtable

Jai Krishna

but how about another col?

Include every column in ur select list

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 03:03:53

quote:
Originally posted by calvinkwoo3000

HI Everyone,

I want to select sum and then divide the by count as below.

col1 col2 col3 col4 .........................
______________________________________________
1 4 empty 5
2 empty 1 5
4 5 empty 5
5 2 2 empty
___________________________________________
(12)/4 = 3.0 (11/3)=3.67 (3)/2=2.50 (15)/3 = 5.0

The col1, col2, col3, col4 ....... is in nvachar type
empty mean nothing inside

SELECT SUM(col1)*1.0/count(col1) as col1,
SUM(col2)*1.0/count(col2) as col2,
SUM(col3)*1.0/count(col3) as col3,
SUM(col4)*1.0/count(col4) as col4
FROM Table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-08 : 03:15:28

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 03:22:30

What is wrong with Average?

SELECT	AVG(1.0E * NULLIF(Col1, '')) AS Col1,
	AVG(1.0E * NULLIF(Col2, '')) AS Col2,
	AVG(1.0E * NULLIF(Col3, '')) AS Col3,
	AVG(1.0E * NULLIF(Col4, '')) AS Col4
FROM	Table1

E 12°55'05.63"
N 56°04'39.26"

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-01-14 : 00:43:04

Thanks all of you.
i get it alreaady

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 02:19:53

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources