0

Good afternoon,

I'm looking to find the sum of the total column in the following query:

SELECT * 
FROM  `btb_picks` 
WHERE  `player_name` =  'bryan'
ORDER BY  `total` 
LIMIT 0 , 3

The table has a total of 9 records where player_name='bryan' 

Position Golfer Name    Round 1 Round 2 Round 3 Round 4 Today   Through Total   Player Name
T4       Lee Westwood   67      12:25   -       -       -5      F       -5      bryan
T4       Justin Rose    67      1:39    -       -       -5      F       -5      bryan
T4       Sergio Garcia  67      1:49    -       -       -5      F       -5      bryan

I would like to gather the sum of the total (-15) of these 3 records

When I run the following query:

SELECT SUM( total ) AS 3_man
FROM  `btb_picks` 
WHERE  `player_name` =  'bryan'
ORDER BY total
LIMIT 0 , 3

It returns:

3_man -13

Which is the sum of the entire total column not the 3 records I'm interested in finding the total for.

When I try the query this way:

SELECT * FROM `btb_picks` WHERE `player_name` in ( SELECT * 
    FROM  `btb_picks` 
    WHERE  `player_name` =  'bryan'
    ORDER BY  `total` 
    LIMIT 0 , 3 );

SQL barks:

1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

How can I get the sum of total in these three records?

Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
evolution9
  • 43
  • 3
  • 8

1 Answers1

0

Try this...

SELECT SUM(s.score) FROM
(SELECT total as score FROM btb_picks WHERE player_name = 'bryan' ORDER BY total LIMIT 0,3) s
Ruslan
  • 2,691
  • 1
  • 19
  • 29