Select Average from MySQL Table with Limit

Select average from MySQL table with LIMIT

I think this is what you're after:

SELECT AVG(items.price)
  FROM (SELECT t.price
          FROM TABLE t
         WHERE t.price > '0' 
           AND t.item_id = '$id'
      ORDER BY t.price
         LIMIT 5) items

It will return the average of the 5 lowest prices - a single answer.

MySQL AVG ... LIMIT returns total AVG

You need to use subquery:

SELECT AVG(b) 
FROM (SELECT b
      FROM table 
      ORDER BY table.a ASC 
      LIMIT 3) sub

EDIT:

Without subquery the order of execution is like:

1. FROM
2. AVG (AVG is calculated using all values)
3. ORDER BY (but there is only one value)
4. LIMIT (LIMIT 3 on one value do nothing)

With subquery the order of execution is like:

1. FROM
2. ORDER BY
3. LIMIT (only 3 values)
4. outer query AVG (average is calculated using only 3 values)

More info: Logical query processing (TOP/OFFSET FETCH is the same as LIMIT).

AVG with LIMIT and GROUP BY

You can try this.

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

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

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 |

SQL - How can I find the average of an SQL statement with a LIMIT?

You need to use a subquery:

SELECT avg(value)
FROM (select value
      from que
      LIMIT 10
     ) q;

Do note, however, that use of limit without an order by produces arbitrary results -- there is no definition of the "first ten" records in a table.

problems with AVG and LIMIT SQL

You are missing a GROUP BY statement, and you may want to remove the DISTINCT. You need something along the lines of:

SELECT USER_NAME, USER_REVIEWS, AVG(REVIEW_STARS)
FROM TRIPADVISOR
GROUP BY USER_NAME, USER_REVIEWS
ORDER BY USER_REVIEWS desc
limit 20;

How to limit SQL-result and take avg?

In the following SQLite query we compute period for each row and use that to group the results by:

SELECT AVG(humidity) AS avgHumidity,
       AVG(temperature) AS avgTemperate,
       AVG(pressure) AS avgPressure,
       AVG(voltage) AS avgVoltage,
       round(100*(julianday(datetime)-julianday(:datetimestart))/
             (julianday(:datetimeend)-julianday(:datetimestart))) AS period   
FROM measurement 
WHERE nodeId = :nodeId AND 
      datetime BETWEEN :datetimestart AND :datetimeend
GROUP BY period

So basically you need to group your results. Several notes:

• I used the julianday() function to be able to add, subtract and
  
  divide, this might not be the correct way to do this in SQLite3.
• I reuse :datetimestart and :datetimeend several times. In MySQL
  
  this is not allowed. I don't know if it is allowed in SQLite3. If it
  is not simply rename them :datetimeend1, :datetimeend2 etc.
• This is only a untested push in the right direction. You need to work out the details yourself.

SQL - MySQL - average group by and limit problems

Somewhat painfully. I think the easiest way is to use variables. The following query enumerates the readings for each instrument:

select l.*,
       (@rn := if(@i = instname, @rn + 1, 
                  if(@i := instname, 1, 1)
                 )
       ) as rn
from log l cross join
     (select @i := '', @rn := 0)
order by instname, id desc;

You can then use this as a subquery to do your calculation:

select instname, avg(timediff)
from (select l.*,
             (@rn := if(@i = instname, @rn + 1, 
                        if(@i := instname, 1, 1)
                       )
             ) as rn
      from log l cross join
           (select @i := '', @rn := 0)
      order by instname, id desc
     ) l
where rn <= 10
group by instname;

---

Related Topics