July 16, 2008 at 7:12 am
I am trying to figure a average on rows. I have been able to do the columns with the AVG function. I need to do the same on rows but do not want nulls or 0 to be included in figuring the average. How can this be done?
July 16, 2008 at 7:28 am
Hi Kenpet,
Its not optimized solution but can solve your problem:
DECLARE @T TABLE (ID int, NAME varchar, Amount1 int, Amount2 int,Amount3 int)
INSERT INTO @T
SELECT 1, 'A', 100, 20, NULL UNION
SELECT 2, 'B', 200, 50, 70 UNION
SELECT 3, 'A', NULL, NULL, NULL UNION
SELECT 4, 'C', 100, 40, 100 UNION
SELECT 5, 'D', 50, 100, 150
SELECT ID,NAME,Amount1,Amount2,Amount3,
(COALESCE(Amount1,0)+COALESCE(Amount2,0)+COALESCE(Amount3,0))/
CASE WHEN Amount1 IS NULL AND Amount2 IS NULL AND Amount3 IS NULL THEN 1 ELSE
(Case When Amount1>0 Then 1 Else 0 end+Case When Amount2>0 Then 1 Else 0 end+Case When Amount3>0 Then 1 Else 0 end) END RowAvg
FROM @T
July 16, 2008 at 7:30 am
[font="Verdana"]Will you explain what exactly you wants to do? What do you mean Avg on Row instead of Col? Altimately the values are going to be resides in cell.
Mahesh[/font]
MH-09-AM-8694
July 16, 2008 at 7:45 am
You can use Unpivot (check it out in Books Online) to turn the row into a column, then use Where to dump 0 and null, then use Avg to average it.
That's probably the easiest way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2008 at 8:04 am
NULL are automatically dumped with UNPIVOT.
SELECTu.Name,
AVG(u.theAmount) AS Average
FROM@T AS s
UNPIVOT(
theAmount
FOR theCol IN ([Amount1], [Amount2], [Amount3])
) AS u
WHEREu.theAmount > 0
GROUP BYu.Name
ORDER BYu.Name
N 56°04'39.16"
E 12°55'05.25"
July 16, 2008 at 8:45 am
I have a table
FastRead int
MornRead int
AfterRead int
EveRead int
I will have many rows with time. I want to get an average for each row not including 0 or null in the average.
Thanks,
kenpet
July 16, 2008 at 9:02 am
Yes, we understand what you're trying to do. Unpivot and Avg will do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply