| Author |
 Topic  |
|
|
igator210
Starting Member
5 Posts |
 Posted - 05/11/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/11/2012 : 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
India
311 Posts |
Posted - 05/12/2012 : 10:00:35
|
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
Vijay is here to learn something from you guys. |
|
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/12/2012 : 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. |
Edited by - vijays3 on 05/12/2012 10:49:14 |
|
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/12/2012 : 11:02:21
|
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;
Vijay is here to learn something from you guys. |
Edited by - vijays3 on 05/12/2012 11:02:48 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/12/2012 : 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.0CREATE TABLE #tmp (id FLOAT);
INSERT INTO #tmp VALUES (1),(NULL),(2);
SELECT AVG(id) FROM #tmp;
DROP TABLE #tmp; |
|
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/13/2012 : 07:55:30
|
Ok ,I got it ,Thanks
Vijay is here to learn something from you guys. |
|
|
|
igator210
Starting Member
5 Posts |
Posted - 05/14/2012 : 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
|
|
|
| |
Topic |
|
|
|
Averaging specific entries
