CODEWITHSUNDEEP
sqlsql-servert-sqlaverage
Phil Ashby
Phil Ashby

Reputation: 23

Calculating an Avg in SQL excluding the current row

I have a DB where certain records are tagged with an ID and I want create a view that contains the Average of all those records with the same ID, EXCLUDING the current record. For example, if my data looks like this:

ROW - ID - Value
1     1    20
2     1    30
3     1    40
4     2    60
5     2    80
6     2    40
7     3    50
8     3    20
9     3    40

My view needs to calculate the average of every row with the same ID, EXCLUDING the row it's on, so my output would look something like this:

ROW - ID - Value AVG
1     1    20    35
2     1    30    30
3     1    40    25
4     2    60    60
5     2    80    50
6     2    40    70
7     3    50    30
8     3    20    55
9     3    40    35

So, in the case of row 3, it's extracted rows 1 and 2, as they have the same ID and given me the avg of their values - 25.

I'm gone round the houses on this for a while now, but can't seem to nail it. Any help would be appreciated.

Upvotes: 2

Views: 3158

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81970

One Option if you have window functions

Declare @YourTable table (ROW int,ID int,Value int)
Insert Into @YourTable values
(1,     1,    20),
(2,     1,    30),
(3,     1,    40),
(4,     2,    60),
(5,     2,    80),
(6,     2,    40),
(7,     3,    50),
(8,     3,    20),
(9,     3,    40)

Select *
      ,Avg = (sum(value) over (Partition By ID)-Value)/NullIf((sum(1) over (Partition By ID)-1),0)
 From  @YourTable

Another Option is a OUTER APPLY

Select A.*
      ,B.*
 From  @YourTable A
 Outer Apply (Select Avg=avg(value) 
               From  @YourTable 
               where ID=A.ID and Row<>A.Row
              ) B

Both Return

enter image description here

Upvotes: 4

WarNaX
WarNaX

Reputation: 83

This query works for me:

select t1.row, t1.id, t1.value, (select avg(value) from test_table as t2 where t1.id = t2.id and t1.row != t2.row) as avg from test_table as t1;

Data in table created by me (i assume is simmilar to Yours):

mysql> select * from test_table;
+-----+------+-------+
| row | id   | value |
+-----+------+-------+
|   1 |    1 |    20 |
|   2 |    1 |    30 |
|   3 |    1 |    40 |
|   4 |    2 |    60 |
|   5 |    2 |    80 |
|   6 |    2 |    40 |
|   7 |    3 |    50 |
|   8 |    3 |    20 |
|   9 |    3 |    40 |
+-----+------+-------+

Result of query:

+-----+------+-------+---------+
| row | id   | value | avg     |
+-----+------+-------+---------+
|   1 |    1 |    20 | 35.0000 |
|   2 |    1 |    30 | 30.0000 |
|   3 |    1 |    40 | 25.0000 |
|   4 |    2 |    60 | 60.0000 |
|   5 |    2 |    80 | 50.0000 |
|   6 |    2 |    40 | 70.0000 |
|   7 |    3 |    50 | 30.0000 |
|   8 |    3 |    20 | 45.0000 |
|   9 |    3 |    40 | 35.0000 |
+-----+------+-------+---------+

Upvotes: 1

Isaiah
Isaiah

Reputation: 680

SELECT t1.gid, AVG(t2.value)
FROM table1 as t1 INNER JOIN
  table1 as t2 ON (t1.gid != t2.gid)
GROUP BY t1.gid;

Basically, join the table to itself on your condition and then group the results based on the first table's key.

This solution should work regardless of what database system you are usimg; there may be minor syntax details to change.

A table like this:

ID | Value
1  | 4
2  | 6
3  | 5

Becomes (when joined):

t1.ID | t2.ID | t1.Value | t2.Value
1     | 2     | 4        | 6
1     | 3     | 4        | 5
2     | 1     | 6        | 4
2     | 3     | 6        | 5
3     | 1     | 5        | 4
3     | 2     | 5        | 6

And, then the aggregate of the grouped rows yields the wanted values.

Upvotes: 1

Related Questions