CODEWITHSUNDEEP
phpmysql
rob melino
rob melino

Reputation: 781

select the 3 highest values using php mysql query

I have a table like this:

`id|value

1|2

2|8

3|5

4|6

5|10

6|7`

I need a query to pull AND sum the 3 highest values. So the correct query would pull the following:

3 highest:

5|10 2|8 6|7

Sum of 3 highest values = 25

I feel like this should be pretty simple but i'm having a tough time! Thanks for your help

Upvotes: 0

Views: 2824

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

To have MySQL return highest 3 values and their Sum in a 4th row, you can use (aasuming that id is the Primary Key of the table):

SELECT id, SUM(value)
FROM
  ( SELECT id, value
    FROM TableX
    ORDER BY value DESC
    LIMIT 3
  ) AS tmp
GROUP BY id 
WITH ROLLUP ;

Upvotes: 0

kitti
kitti

Reputation: 14794

I think you need to wrap this in a subquery:

SELECT SUM(value) AS total FROM (
    SELECT value FROM table
    ORDER BY value DESC
    LIMIT 3
);

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

SELECT SUM(Value) AS SumOfTop3Values
FROM (
    SELECT Value
    FROM Table
    ORDER BY Value DESC
    LIMIT 3
) AS sub

Upvotes: 1

Related Questions