SUM first three rows per ID

Dev Shed Forums Sponsor:

November 20th, 2012, 05:02 PM

oach

Contributing User

Join Date: Apr 2001

Location: knee deep in sh*t

Posts: 251

Time spent in forums: 2 Days 17 h 56 m 46 sec
Reputation Power: 64

SUM first three rows per ID

Below is my data set, I wanted to be able to take the sum of the first three instances of the ppl per id. So ID 1020891 = 40, but 1020883=103 not 221 and so on and so forth for each unique ID. Is there a way to do this in mysql?

Thanks in advance.

Code:

ID	inquiry_date		par_id	ppl	rank	dgr_id	transfer
1020891	2012-11-20 16:15:19	10528	40	4	115204	n
1020887	2012-11-20 16:09:41	28181	40	1	428256	n
1020883	2012-11-20 16:05:36	26197	36	3	389592	n
1020883	2012-11-20 16:05:36	5641	37	5	49544	n
1020883	2012-11-20 16:05:36	19063	30	6	201640	n
1020883	2012-11-20 16:05:36	26297	30	8	391300	n
1020883	2012-11-20 16:05:36	26300	30	9	391328	n
1020883	2012-11-20 16:05:36	26298	30	10	409867	n
1020883	2012-11-20 16:05:36	26350	28	11	392192	n
1020878	2012-11-20 16:02:12	5659	37	2	49649	n
1020875	2012-11-20 15:59:08	5650	37	1	49600	n
1020871	2012-11-20 15:57:07	26042	28	4	385618	n

November 21st, 2012, 02:29 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Which rows are 'first', and why?
What's your PRIMARY KEY?

Last edited by cafelatte : November 21st, 2012 at 02:31 AM.

November 21st, 2012, 09:36 AM

oach

Contributing User

Join Date: Apr 2001

Location: knee deep in sh*t

Posts: 251

Time spent in forums: 2 Days 17 h 56 m 46 sec
Reputation Power: 64

In this case the primary key is id, and what's considered the "first" row is rank, so the 3 highest (lowest number) ranked rows for each id is to be summed.

-Neal

November 21st, 2012, 11:06 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

id is not unique, so it cannot be PRIMARY, but based upon what you have said, we'll assume that the there is instead a compound PK formed on (id,rank)...

Code:

SELECT id
     , SUM(ppl) ttl
  FROM
     ( SELECT x.*
            , COUNT(*)rnk 
         FROM my_table x 
         JOIN my_table y 
           ON y.id = x.id 
          AND y.rank <= x.rank 
        GROUP 
           BY id,rank
     ) a 
 WHERE rnk <=3 
 GROUP 
    BY id;

...or something like that

Comments on this post

oach agrees: Thank you!

November 26th, 2012, 01:58 PM

oach

Contributing User

Join Date: Apr 2001

Location: knee deep in sh*t

Posts: 251

Time spent in forums: 2 Days 17 h 56 m 46 sec
Reputation Power: 64

That worked great! Thank you.

SUM first three rows per ID

Developer Shed Advertisers and Affiliates