Jackddddd Jackddddd - 1 month ago 6
SQL Question

Get the average from a column, then filter out all the rows whose number is smaller than the average, and then count the number of row

This is sql question i have been working on for a while with no result.

The schema is

CREATE TABLE states (
statecode text primary key,
population_2010 integer,
population_2000 integer,
population_1950 integer,
population_1900 integer,
landarea real,
name text,
admitted_to_union text
);
CREATE TABLE counties(
name text,
statecode text references states(statecode),
population_1950 integer,
population_2010 integer
);
CREATE TABLE senators(
statecode text references states(statecode),
name text primary key,
affiliation text,
took_office integer,
born integer);
CREATE TABLE committees(
id text primary key,
parent_committee text references committees(id),
name text,
chairman text references senators(name),
ranking_member text references senators(name)
);


The question is:
return a count of how many states have more than the average number of counties

I know how to calculate the average:

select avg(state_count)
from
(select count(*) as state_count
from counties C
group by C.statecode)


But i'm wondering if i can reuse the subquery.

like this(the following one does not work ):

select count(*) as state_count
from counties C
group by C.statecode
having count(*) > avg(state_count)


This query still does not count the number of rows, is there an elegant way?

EDIT:
Just found a sample answer

SELECT COUNT(*)
FROM (SELECT statecode, COUNT(*) AS counts
FROM counties
GROUP BY statecode) s
WHERE s.counts > (SELECT AVG(t.counts)
FROM (SELECT COUNT(*) AS counts
FROM counties
GROUP BY statecode) t);


But i am still curious if there is a better way?

Answer

The uneconomical part of sample answer that you have provided is this

SELECT COUNT(*) AS counts
FROM counties 
GROUP BY statecode

This part appears twice, once for the purpose of calculating average and once for finding counts which are greater than average. So here is my attempt using CTE which reuses the above piece of code for both the purposes:

WITH c AS
(
   SELECT COUNT(*) AS counts
   FROM counties 
   GROUP BY statecode
)
SELECT COUNT(*) FROM c WHERE counts > (SELECT  Avg(CAST(counts AS decimal)) 
                                       FROM c)