CODEWITHSUNDEEP
phpmysql
EnexoOnoma
EnexoOnoma

Reputation: 8836

How to count the rows that are above average in mySQL?

This is my query, however I can not succeed on showing the number of pages that their percent_diff is above the column's average.

What am I missing here?

SELECT count(page) from
(
select * FROM pages_diff where company_id = 3 
group by page
having percent_diff > avg(percent_diff)
) nested

Upvotes: 1

Views: 2156

Answers (2)

poofeg
poofeg

Reputation: 71

I think this will be the solution:

SELECT count(page) FROM pages_diff
WHERE company_id = 3 AND percent_diff > (
  SELECT avg(percent_diff) FROM pages_diff
  WHERE company_id = 3
)

Upvotes: 2

Norbert
Norbert

Reputation: 6084

There are a few ways to do this. With your base query the fastest (to write) would be something like this:

SELECT above_avg,below_avg from
(
select count(page) as above_avg FROM pages_diff where company_id = 3 
group by page
having percent_diff > avg(percent_diff)
) nested_above
,
(
select count(page) as below_avg FROM pages_diff where company_id = 3 
group by page
having percent_diff < avg(percent_diff)
) nested_below

Alternative you could just get avg and then use if statement. You would still go through the table twice (once to get avg, once to do count on two columns at once)

Upvotes: 0

Related Questions