CODEWITHSUNDEEP
sqlsql-serverdatabasestored-proceduressql-server-2008-r2
Gayan
Gayan

Reputation: 1553

SQL - get row for same table

I have a table. 

select * from Group

Owner    Member1   Member1_Value    Member2  Member2_Value
Kelly    Nicol         2             Julie       4
Nicol    Julie         4             Kelly       3
Julie    Kelly         5             Nicol       3

Now I need to get Avege value from our Members for Owners.

Owner    Member1   Member1_Value    Member2  Member2_Value    Avg
Kelly    Nicol         2             Julie       5            3.5
Nicol    Julie         4             Kelly       2            2.5
Julie    Kelly         5             Nicol       3            4.5

Eg: Kelly's Avg 3.5 comes from Nicol gave 2 and Julia gave 5 and Avg is 3.5. as same as Nicol got Avg 2.5 because Kelly gave 2 and Julie gave 3.

So how can I get that Avg colum from one sql statment

Appreciate your answers

Thanks

Upvotes: 0

Views: 43

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This seems like a very complicated data structure. Normally, having columns like member1 and member2 is a sign of poor database design. You should really normalize the table.

You seem to want the contribution of the other member from matching rows in the table. I think the following does what you want:

select g.owner, (gm1.member2_value + gm2.member1_value) / 2.0
from group g join
     group gm1
     on g.owner = gm1.member1 join
     group gm2
     on g.owner = gm2.member2;

Upvotes: 1

Juan
Juan

Reputation: 3705

I would say that what you want is this but you should consider normalising a little bit that table in order to be able to do this kinds of queries easier:

SELECT
   Name, AVG(Value) AS Value
FROM
(
    SELECT
       Member1 AS Name,
       Member1_Value As Value
    FROM Group
    UNION ALL
    SELECT
       Member2 AS Name,
       Member2_Value As Value
    FROM Group
 ) Normalised
 GROUP BY Name

A better table structure for this will by

Member(MemberId, Name) Group(GroupId) GroupMember(MemberId, GroupId) Qualifications(ProvidingMemberId, ReceivingMemberId, Value)

Or something similar

Upvotes: 1

Deep
Deep

Reputation: 3202

If I understood it correctly then it should work :

select *, cast((Member1_Value + Member2_Value) as Decimal(10,2))/2 [Avg] from Group

Upvotes: 1

Related Questions