Find Min Value and value of a corresponding column for that result

select, sql

Solution

For almost everything you can use a simple groupby, but as you need "the same address than the row where the minimum datejoined is" is a little bit tricker and you can solve it in several ways, one is a subquery searching the address each time

SELECT
   X.*, 
   (select Address 
    from #tmp t2 
    where t2.MemberID = X.memberID and 
    t2.DateJoined = (select MIN(DateJoined) 
                     from #tmp t3 
                     where t3.memberID = X.MemberID)) 
FROM
   (select MemberID, 
           Name,  
           MIN(DateJoined) as DateJoined, 
           MAX(DateQuit) as DateQuit, 
           SUM(PointsEarned) as PointEarned
from #tmp t1
group by MemberID,Name
) AS X

` Or other is a subquery with a Join

SELECT
   X.*, 
   J.Address 
FROM
(select 
         MemberID, 
         Name,  
         MIN(DateJoined) as DateJoined, 
         MAX(DateQuit) as DateQuit, 
         SUM(PointsEarned) as PointEarned
from #tmp t1
group by MemberID,Name
) AS X
JOIN #tmp J ON J.MemberID = X.MemberID AND J.DateJoined = X.DateJoined

Problem

I have a table of user data in my SQL Server database and I am attempting to summarize the data. Basically, I need some min, max, and sum values and to group by some columns Here is a sample table: ``` Member ID | Name | DateJoined | DateQuit | PointsEarned | Address 00001 | Leyth | 1/1/2013 | 9/30/2013 | 57 | 123 FirstAddress Way 00002 | James | 2/1/2013 | 7/21/2013 | 34 | 4 street road 00001 | Leyth | 2/1/2013 | 10/15/2013| 32 | 456 LastAddress Way 00003 | Eric | 2/23/2013 | 4/14/2013 | 15 | 5 street road ``` I'd like the summarized table to show the results like this: ``` Member ID | Name | DateJoined | DateQuit | PointsEarned | Address 00001 | Leyth | 1/1/2013 | 10/15/2013 | 89 | 123 FirstAddress Way 00002 | James | 2/1/2013 | 7/21/2013 | 34 | 4 street road 00003 | Eric | 2/23/2013 | 4/14/2013 | 15 | 5 street road ``` Here is my query so far: ``` Select MemberID, Name, Min(DateJoined), Max(DateQuit), SUM(PointsEarned), Min(Address) From Table Group By MemberID ``` The `Min(Address)` works this time, it retrieves the address that corresponds to the earliest DateJoined. However, if we swapped the two addresses in the original table, we would retrieve "123 FirstAddress Way" which would not correspond to the 1/1/2013 date joined.

Original source