CODEWITHSUNDEEP
sqlsql-serveraverage
Diogo
Diogo

Reputation: 1603

How to get the average from 3 values on a table on SQL Server?

I have the following situation on my database:

        id:   |   second id:  |  value 
Row 1:  1     |    1          |   123
Row 2:  1     |    2          |   234
Row 3:  1     |    3          |   456
Row 4:  2     |    1          |   987
Row 5:  2     |    2          |   876
Row 6:  2     |    3          |   765
Row 7:  3     |    1          |   345
Row 8:  3     |    2          |   678
Row 9:  3     |    3          |   543
Row 10: 4     |    1          |   345 
...

For each id I have 3 second ids, they are always 1,2 and 3. What I need to do is get the average of this 3 values (contemplating the 3 valus for id) and get the average of them. I need to do this with a long table (I was wondering about creating a specific view to do this).

Any help will be welcome.

(Sorry for my question, but i really begginer on SQL Server)

Upvotes: 1

Views: 2014

Answers (6)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

If you're wanting to add these average rows in your output, something like this might work (here, I'm creating the sample data inline, and aliasing it as T - it could easily select the basic data from a table instead):

select ID,COALESCE(CONVERT(varchar(10),subid),'avg'),AVG(val) from (
select 1 as id,1 as subid,123 as val union all
select 1,2,234 union all
select 1,3,456 union all
select 2,1,987 union all
select 2,2,876 union all
select 2,3,745
) t
group by ID,subid with rollup
having ID is not null

Result:

1   1   123
1   2   234
1   3   456
1   avg 271
2   1   987
2   2   876
2   3   745
2   avg 869

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

If you want accuracy, use

SELECT second_id, AVG(value*1) FROM table GROUP BY second_id

Upvotes: 0

JNK
JNK

Reputation: 65187

EDIT:

Per your comment, try:

SELECT id, AVG(value)
FROM MyTable
GROUP BY id

Upvotes: 0

user554546
user554546

Reputation:

If you don't have any kind of window function, a self-join should also work:

select id, second_id, value, avg_val_over_id
from(
    select id, second_id, value
    from table
    )a
join(
    select id, avg(value) as avg_val_over_id
    from table
    group by id
    )b
using(id);

Upvotes: 1

cwb
cwb

Reputation: 925

I'm not sure I understand what you're after, but if you want the average for each of the second_ids try:

SELECT second_id, AVG(value)
FROM table
GROUP BY second_id

Upvotes: 0

CristiC
CristiC

Reputation: 22698

Just use AVG and group by each id only.

SELECT id, AVG(value)
FROM MyTable
GROUP BY id

Upvotes: 4

Related Questions