Reputation: 993
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
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
Upvotes: 1
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
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
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