Microsoft SQL Server forum - Returning average of multiple rows in a table join

Author

Returning average of multiple rows in a table join

StraightEight

2005-06-24, 7:23 am

Hi,

I'm am looking for a little help. I need to create a SQL view which
joins a few tables, and I need to return an average for a particular
column where a second duplicate ID exists...

Heres an example of how the results could be returned...

ID | Name | Order No. | Value
---+------+-----------+---------
5 | test | 1234 | 3
5 | test2| 1234 | 4
5 | test3| 1234 | 3
5 | void | 1235 | 5
5 | void2| 1235 | 6
5 | void3| 1235 | 5
5 | void4| 1235 | 7

ID is my main join which joins the tables

Name is a unique name

Order No is the same for the different names, I only need to return one
row with this order no, and the first name (the rest are irrelevant)

Value is the field which I wish to return as an average of all 3, 4 or
however many rows is returned and share the same order no. This is
where I get totally lost as I am pretty new to SQL. Can anyone provide
any help on how I would go about limiting this query to the unique
order no's and returning the average of the value field, and I can take
it from there with my own tables.

Thanks for your help

str8

David Portas

2005-06-24, 7:23 am

Something like this for example?

SELECT order_no, MIN(name), AVG(value)
FROM YourTable
GROUP BY order_no

--
David Portas
SQL Server MVP
--

StraightEight

2005-06-24, 7:23 am

I can't get that to work unless I only try to select the values with
the aggregates, although i don't think this is entirely what I'm
after....what I'm need to do is, based on my initial view...

ID | Name | Order No. | Value
---+------+-----------+-------=AD--
5 | test | 1234 | 3
5 | test2| 1234 | 4
5 | test3| 1234 | 3
5 | void | 1235 | 5
5 | void2| 1235 | 6
5 | void3| 1235 | 5
5 | void4| 1235 | 7

return something more like this...

ID | Name | Order No. | Value
---+------+-----------+-------=AD--
5 | test | 1234 | 3.33
5 | void | 1235 | 5.75

So for each unique order no, I return the details in the first row,
except value column, where I return the average of all values in all
rows which share the same order no.

I hope this makes sense? Thanks for your help so far!

str8

David Portas

2005-06-24, 9:23 am

Based on what you posted it seems like I just left out one column:

SELECT id, order_no, MIN(name), AVG(value)
FROM YourTable
GROUP BY id, order_no

but maybe there could be more than one ID per order_no? That's why it
helps if you can include DDL (CREATE TABLE statements including keys
and constraints) with your questions so that we don't have to guess at
the keys and dependencies in your data.

Maybe you also have some other columns you didn't tell us about. To
answer you properly we'll need a better explanation of what you mean by
the "first" row for the order no. Tables have no fixed concept of
order. A table is an unordered set. So you need some other column or
columns that unambiguously identifies which row comes first. For
example:

SELECT id, order_no, /* ... other columns */,
(SELECT AVG(value)
FROM YourTable
WHERE order_no =3D T.order_no)
FROM YourTable AS T
WHERE ord =3D
(SELECT MIN(ord)
FROM YourTable
WHERE order_no =3D T.order_no)

The following article explains the best way to get help with a problem
such as this:
http://www.aspfaq.com/etiquett=ADe.asp?id=3D5006

Hope this helps.

--=20
David Portas=20
SQL Server MVP=20
--

Sponsored Links