On 17 Aug 2004 15:53:39 GMT, Jimmy Tran wrote:
Hi All,
I have a table below and I want to design a query to pull all the
members from the TABLE into a Query Result and into a single column with
points assigned appropriately, but I am having a lot of difficulties
doing this. Any help is greatly appreciated.
TABLE
MEMBER1 MEMBER2 POINTS
Joe Don 2
Macy 1
Jack Nick 2
Joe Rob 2
This is a result I would like to generate from the query
Query Result
MEMBERS Total Points
Joe 2
Don 1
Macy 1
Jack 1
Nick 1
Rob 1
Thank you,
Jim
You should always try to post DDL and sample inserts when you ask questions
here; it makes your problem much clearer and makes it easier for those who
wish to help -- we can just paste into query analyzer and begin.
Below is complete DDL, inserts, your solution and its results, as copied
from query analyzer:
create table T1 (
MEMBER1 varchar(20) null,
MEMBER2 varchar(20) null,
POINTS int
)
insert into T1 (MEMBER1, MEMBER2, POINTS)
values ('Joe', 'Don', 2)
insert into T1 (MEMBER1, MEMBER2, POINTS)
values ('Macy', null, 1)
insert into T1 (MEMBER1, MEMBER2, POINTS)
values ('Jack', 'Nick', 2)
insert into T1 (MEMBER1, MEMBER2, POINTS)
values ('Joe', 'Rob', 2)
select MEMBERS, sum(POINTS) as [Total Points]
from (
select MEMBER1 as MEMBERS,
POINTS/case when MEMBER2 is null then 1 else 2 end as POINTS
from T1 where MEMBER1 is not null
union all
select MEMBER2 as MEMBERS,
POINTS/case when MEMBER1 is null then 1 else 2 end as POINTS
from T1 where MEMBER2 is not null
) as T2
group by MEMBERS
order by sum(POINTS) DESC
MEMBERS Total Points
-------------------- ------------
Joe 2
Macy 1
Nick 1
Rob 1
Don 1
Jack 1
I made the assumption from your example that if either member is null, the
non-null member gets all the points; otherwise they are split evenly
between the two members. If in fact it will always be one point per
non-null member per row, then the query is a bit simpler:
select MEMBERS, count(*) as [Total Points]
from (
select MEMBER1 as MEMBERS
from T1 where MEMBER1 is not null
union all
select MEMBER2 as MEMBERS
from T1 where MEMBER2 is not null
) as T2
group by MEMBERS
order by count(*) DESC
