AVG with LIMIT and GROUP BY

I'm looking to make a SQL query, but I can't do it... and I can't find an example like mine.

I have a simple table People with 3 columns, 7 records :

I'd like to get for each team, the average points of 2 bests people.

My Query:

SELECT team
, (SELECT AVG(point)
FROM People t2
WHERE t1.team = t2.team
ORDER
BY point DESC
LIMIT 2) as avg
FROM People t1
GROUP
BY team


Current result: (average on all people of each team)

Apparently, it's not possible to use a limit into subquery. "ORDER BY point DESC LIMIT 2" is ignored.

Result expected:

I want the average points of 2 bests people (with highest points) for each team, not the average points of all people of each team.

How can I do that? If anyone has any idea..

I'm on MySQL Database

Link of Fiddle : http://sqlfiddle.com/#!9/8c80ef/1

Thanks !

2 Answers
2

You can try this.

try to make a order number by a subquery, which order by point desc.

order by point

then only get top 2 row by each team, if you want to get other top number just modify the number in where clause.

top

where

CREATE TABLE `People` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`team` varchar(20) NOT NULL,
`point` int(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `People` (`id`, `name`, `team`, `point`) VALUES
(1, 'Luc', 'Jupiter', 10),
(2, 'Marie', 'Saturn', 0),
(3, 'Hubert', 'Saturn', 0),
(4, 'Albert', 'Jupiter', 50),
(5, 'Lucy', 'Jupiter', 50),
(6, 'William', 'Saturn', 20),
(7, 'Zeus', 'Saturn', 40);

ALTER TABLE `People`
ADD PRIMARY KEY (`id`);


ALTER TABLE `People`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;


Query 1:

SELECT team,avg(point) totle
FROM People t1
where (
select count(*)
from People t2
where t2.id >= t1.id and t1.team = t2.team
order by t2.point desc
) <=2 ## if you want to get other `top` number just modify this number
group by team


Results:

| team | totle |
|---------|-------|
| Jupiter | 50 |
| Saturn | 30 |


This is a pain in MySQL. If you want the two highest point values, you can do:

SELECT p.team, AVG(p2.point)
FROM people p
WHERE p.point >= (SELECT DISTINCT p2.point
FROM people p2
WHERE p2.team = p.team
ORDER BY p2.point DESC
LIMIT 1, 1 -- get the second one
);


Ties make this tricky, and your question isn't clear on what to do about them.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.