Get Sum of 1st Four Highest Values in Query

[n1concepts]

How can I get the sum of the 1st (highest) four rows which should equal 118 lb, 29oz?

Note: the ounce brings up another issue but I'll deal w/that separately from this one...

 

Here's the current SQL query which show all the rows related to a particular user id:

* Only rows (id162.163.26.279) should be included in the sum function.

mysql> SELECT f.id,f.userid,f.eventid,f.location,f.lb,f.oz from tfish as f where f.userid = 8 ORDER BY lb DESC;
+-----+--------+---------+----------+----+----+
| id  | userid | eventid | location | lb | oz |
+-----+--------+---------+----------+----+----+
| 162 |      8 |       4 |          | 32 |  7 |
| 163 |      8 |       4 |          | 31 | 14 |
|  26 |      8 |       4 |          | 28 |  2 |
| 279 |      8 |       4 |          | 27 |  6 |
|  24 |      8 |       4 |          | 24 | 12 |
|  25 |      8 |       4 |          | 23 |  4 |
|  23 |      8 |       4 |          | 21 |  9 |
+-----+--------+---------+----------+----+----+
7 rows in set

How can this be accomplished?

I tried the LIMIT 4 but it still calculated the entire sum for all rows (as shown below):

mysql> SELECT f.userid,f.eventid,f.location,SUM(f.lb),SUM(f.oz) from tfish as f where f.userid = 8 
GROUP BY f.userid,f.eventid 
HAVING COUNT(*) >= 4
ORDER BY f.userid;
+--------+---------+----------+-----------+-----------+
| userid | eventid | location | SUM(f.lb) | SUM(f.oz) |
+--------+---------+----------+-----------+-----------+
|      8 |       4 |          | 186       | 54        |
+--------+---------+----------+-----------+-----------+
1 row in set

Any suggestions to ONLY calculate the sum of the 1st four (highest) rows?

 

 

[Barand]

Use a subquery

SELECT userid, eventid, location, SUM(lb), SUM(oz) 
from (
    SELECT f.userid,f.eventid,f.location, f.lb, f.oz
    FROM tfish as f 
    WHERE f.userid = 8 
    ORDER BY f.lb DESC
    LIMIT 4
) as firstfour

GROUP BY f.userid,f.eventid 

[n1concepts]

Thanks - Worked perfectly!

I made it more complicated - as usual - than it needed to be!

 

Appreciate your support!

mysql> SELECT userid, eventid, location, SUM(lb), SUM(oz) 
from (
    SELECT f.userid,f.eventid,f.location, f.lb, f.oz
    FROM tfish as f 
    WHERE f.userid = 8 
    ORDER BY f.lb DESC
    LIMIT 4
) as firstfour

GROUP BY userid,eventid;
+--------+---------+----------+---------+---------+
| userid | eventid | location | SUM(lb) | SUM(oz) |
+--------+---------+----------+---------+---------+
|      8 |       4 |          | 118     | 29      |
+--------+---------+----------+---------+---------+
1 row in set

[Barand]

If you want to do all users for an event in one query

SELECT userid, eventid, location, 
    SUM(lb) + FLOOR(SUM(oz)/16) as lb, SUM(oz) MOD 16 as oz 
FROM (
    SELECT f.eventid,f.location, f.lb, f.oz,
    @rank := IF(@prevuser=userid, @rank+1, 1) as rank,
    @prevuser := userid as userid
    FROM tfish as f 
        JOIN (SELECT @prevuser:=0, @rank=0) as init
    ORDER BY userid, f.lb DESC
) as firstfour
WHERE eventid = 4
    AND rank < 5
GROUP BY userid;

results

+--------+---------+----------+------+------+
| userid | eventid | location | lb   | oz   |
+--------+---------+----------+------+------+
|      8 |       4 |          |  119 |   13 |
|      9 |       4 |          |  122 |    0 |
+--------+---------+----------+------+------+