CODEWITHSUNDEEP
phpmysqlsql
Kieran Headley
Kieran Headley

Reputation: 993

Limit rows on MYSQL query calculation

I am trying to limit the amount of rows that are used to do a calculation in SQL;

SELECT    SUM(`position`)/COUNT(`id`) AS `avg`
FROM      rankings_04_06_13
WHERE     site_id = '7'
ORDER BY  `position` ASC
LIMIT     4

However when I do this is doesn't change the answer it is as if the calculation already takes place before it reaches the LIMIT and ORDER BY clauses.

Any ideas?

Upvotes: 1

Views: 1532

Answers (4)

Dek Dekku
Dek Dekku

Reputation: 1461

Here's an example using derived tables. It might be what you're looking for depending on how do you want to apply the limit (you might want to group by position to get the first 4 positions rather than the first 4 rows)

SELECT SUM(`position`)/COUNT(`id`) AS `avg` 
FROM   (SELECT position, id 
        FROM rankings_04_06_13 
        WHERE site_id = '7' 
        ORDER BY `position` ASC 
        LIMIT 4
) AS AVRG

SQL Fiddle

Upvotes: 1

Stephan
Stephan

Reputation: 8090

Try this (like suggested by @DekDekku):

SELECT
    SUM(`position`)/COUNT(`id`) AS `avg`
FROM (
    SELECT    `position`,
              `id`
    FROM      rankings_04_06_13
    WHERE     site_id = '7'
    ORDER BY  `position` ASC
    LIMIT 4
) as tmp

UPDATE: to limit 75% of the rows you can try this (but its not very efficient):

SET @c:=0;    
SELECT
    SUM(`position`)/COUNT(`id`) AS `avg`
FROM (
    SELECT    
        `position`,
        `id`,
        @c:=@c+1,
        MOD(@c,4) as m      
    FROM      
        rankings_04_06_13
    WHERE     
        site_id = '7'
) as tmp
WHERE
    m <> 3

Upvotes: 1

Yadav Chetan
Yadav Chetan

Reputation: 1894

try this 

 SELECT SUM(`position`)/COUNT(`id`) AS `avg`
 FROM   (SELECT position,id from rankings_04_06_13 
 WHERE site_id = '7' 
 ORDER BY  `position` ASC LIMIT 4) as t2

Upvotes: 0

Anooj P
Anooj P

Reputation: 346

SELECT    SUM(`position`)/COUNT(`id`) AS `avg` 
FROM      (SELECT postion,id from rankings_04_06_13  
WHERE     site_id = '7' 
ORDER BY  `position` ASC LIMIT 4) AS subquery;

Upvotes: 0

Related Questions