Geek asks and answers

Find the Min Value and the corresponding column value for this result.

I have a user data table in my SQL Server database and am trying to summarize the data. Basically I need some values ​​of min, max and sum and group by some columns

Here is an example 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 would like the summary table to display the following results:

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 request:

Select MemberID, Name, Min (DateJoined), Max (DateQuit), SUM (PointsEarned), Min (Address) From the GroupID MemberID table

This time Min (Address) works, it gets the address corresponding to the earliest DateJoined. However, if we replaced the two addresses in the source table, we would retrieve 123 FirstAddress Way that would not match the join date 1/1/2013.

Thanks, go ahead

+3
source share
3

, groupby, " , , ", , : - ,

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

` -

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
+1

rank :

SELECT  t.member_id,
        name,
        date_joined,
        date_quit,
        points_earned
        address AS address
FROM   (SELECT member_id
               name, 
               MIN (date_joined) AS date_joined,
               MAX (date_quit) AS date_quit, 
               SUM (points_earned) AS points_earned,
        FROM   my_table
        GROUP BY member_id, name) t
JOIN   (SELECT member_id, 
               address, 
               RANK() OVER (PARTITION BY member_id ORDER BY date_joined) AS rk
        FROM   my_table) addr ON addr.member_id = t.member_id AND rk = 1
0
SELECT DISTINCT st.memberid, st.name, m1.datejoined, m2.datequit, SUM(st.pointsearned), m1.Address
from SAMPLEtable st
LEFT JOIN ( SELECT  memberid
                  , name
                  , MIN(datejoined)
                  , datequit
            FROM    sampletable
          ) m1 ON st.memberid = m1.memberid
LEFT JOIN ( SELECT  memberid
                  , name
                  , datejoined
                  , MAX(datequit)
            FROM    sampletable
          ) m2 ON m1.memberid = m2.memberid
0
source

More articles:


All Articles