#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    knee deep in sh*t
    Posts
    254
    Rep Power
    65

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Which rows are 'first', and why?
    What's your PRIMARY KEY?
    Last edited by cafelatte; November 21st, 2012 at 02:31 AM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    knee deep in sh*t
    Posts
    254
    Rep Power
    65
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    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!
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    knee deep in sh*t
    Posts
    254
    Rep Power
    65
    That worked great! Thank you.

IMN logo majestic logo threadwatch logo seochat tools logo