Averaging specific entries - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Averaging specific entries

Author

Topic

igator210
Starting Member

5 Posts

Posted - 2012-05-11 : 12:01:00

I'm new to the SQL syntax of formulas (stepping over from the Excel world). I've tried internet search and so far nothing has fit my need.

I have a SQL 2008 database with a Access 2010 front end. I have a table that has a lot of values in it in various columns. I need to take the average of three values.

The formula that I have that works is ("A" + "B" + "C")/3, where A, B and C refer to specific table locations in the format where "A" = [A1].[A2].[A3] The problem that I have is if one of the values if Null. I would like to use the AVG() function, but everything that I found online refers to it taking the average of the whole column, and I don't need that.

Is it possible to use the AVG() function to only take specific values in a large table? I tried using Select Avg() From(), but it didn't like that.

Nate

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-11 : 13:08:13

There is no AVG function that can be applied across columns in T-SQL. You can do one or the other in the example below, but I will be the first to admit that these are hacks.

CREATE TABLE #tmp (id int, a FLOAT, b FLOAT, c FLOAT);

INSERT INTO #tmp VALUES (1, 1.1,2.1,3),(2, 7.3,4.1,8),(3, 1,8,2), (4,2,NULL,3);

SELECT id, AVG(x) FROM #tmp UNPIVOT (x FOR y IN (a,b,c))U GROUP BY id;
SELECT id, (COALESCE(a,0)+COALESCE(b,0)+COALESCE(c,0))/
	NULLIF((
		CASE WHEN a IS NULL THEN 0 ELSE 1 END+
		CASE WHEN b IS NULL THEN 0 ELSE 1 END+
		CASE WHEN c IS NULL THEN 0 ELSE 1 END),0 )
FROM
	#tmp

DROP TABLE #tmp;

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 10:00:35

[code]Check this out also
declare @columns int
SELECT @columns =MAX(ORDINAL_POSITION)-1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable'
select id ,SUM(isnull(a,0)+isnull(b,0)+isnull(c,0))/@columns from MyTable group by id

[/code]

Vijay is here to learn something from you guys.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 10:48:07

quote:
Originally posted by sunitabeck

There is no AVG function that can be applied across columns in T-SQL. You can do one or the other in the example below, but I will be the first to admit that these are hacks.
CREATE TABLE #tmp (id int, a FLOAT, b FLOAT, c FLOAT);

INSERT INTO #tmp VALUES (1, 1.1,2.1,3),(2, 7.3,4.1,8),(3, 1,8,2), (4,2,NULL,3);

SELECT id, AVG(x) FROM #tmp UNPIVOT (x FOR y IN (a,b,c))U GROUP BY id;
SELECT id, (COALESCE(a,0)+COALESCE(b,0)+COALESCE(c,0))/
NULLIF((
CASE WHEN a IS NULL THEN 0 ELSE 1 END+
CASE WHEN b IS NULL THEN 0 ELSE 1 END+
CASE WHEN c IS NULL THEN 0 ELSE 1 END),0 )
FROM
#tmp

DROP TABLE #tmp;

I have made some value change in your code so that all should be divided by 3.

SELECT id, (COALESCE(a,0)+COALESCE(b,0)+COALESCE(c,0))/
	NULLIF((
		CASE WHEN a IS NULL THEN 1 ELSE 1 END+
		CASE WHEN b IS NULL THEN 1 ELSE 1 END+
		CASE WHEN c IS NULL THEN 1 ELSE 1 END),0 )
FROM
	MyTable

Vijay is here to learn something from you guys.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 11:02:21

[code]Small change in code:

SELECT ID, avg(U.x) FROM
(SELECT ID,isnull(a,0)a,isnull(b,0)b,isnull(c,0)c FROM MyTable)T
UNPIVOT (x FOR y IN (a,b,c))U GROUP BY id;
[/code]

Vijay is here to learn something from you guys.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-12 : 16:01:34

quote:

Originally posted by vijays3

I have made some value change in your code so that all should be divided by 3.

SELECT id, (COALESCE(a,0)+COALESCE(b,0)+COALESCE(c,0))/
	NULLIF((
		CASE WHEN a IS NULL THEN 1 ELSE 1 END+
		CASE WHEN b IS NULL THEN 1 ELSE 1 END+
		CASE WHEN c IS NULL THEN 1 ELSE 1 END),0 )
FROM
	MyTable

I was trying to make it simulate the AVG function in SQL which excludes null values when calculating the average. Just like in the example below where the average comes out to be 1.5 rather than 1.0

CREATE TABLE #tmp (id FLOAT);
INSERT INTO #tmp VALUES (1),(NULL),(2);
SELECT AVG(id) FROM #tmp;
DROP TABLE #tmp;

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-13 : 07:55:30

[code] Ok ,I got it ,Thanks[/code]

Vijay is here to learn something from you guys.

igator210
Starting Member

5 Posts

Posted - 2012-05-14 : 11:23:32

Thanks for the information. I tried it out a bit, but I don't think it is quite what I need. I also found out I have a bit of leeway in that the database auto set to 0 and not null.

This database is a commercial database to handle dynamic data. There are built in default calculations, but we have the a limited ability to design our own calculations. Not that it will mean too much, but my exact formula that I currently have is:

([Water].[PCB].[Peak1]+[Water].[PCB].[Peak2]+[Water].[PCB].[Peak3])/3

Because this being done in the front end of the database, I have to write everything as a single line. I was hoping to use the AVG function, but I think I might have to use the CASE WHEN statement. In Excel I'd write it...

=IF(AND(C13>0,C14>0,C15>0),(C13+C14+C15)/3,IF(OR(AND(C13=0,C14=0),AND(C13=0,C15=0),AND(C14=0,C15=0)),(C13+C14+C15)/1,(C13+C14+C15)/2))

Where all the cell assignments I'd have to replace with the [A1].[A2.][A3] SQL structure.

Any good way to translate the Excel formula to TSQL?

Nate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources