Can't find the lowest value from percentage query

Question

I'm looking for the lowest value from percentage value from one subquery in PostgreSQL.

This is what I tried:

-- Objective, find lowest value from percent subquery to return only one row

WITH lowest_pct AS 
    (
        SELECT
              c2010.geo_name, -- Geographical name
              c2010.state_us_abbreviation AS state_name, --State abbreviation 
        
        -- p0010001 = total population for each county used in 2010 and 200
        -- Here, it's percentage change query
              ROUND(((CAST(c2010.p0010001 AS NUMERIC(8,1)) - c2000.p0010001) / c2010.p0010001)*100 , 1)
                AS pct_change
        
        FROM us_counties_2010 AS c2010 INNER JOIN us_counties_2000 AS c2000
        ON c2010.state_fips = c2000.state_fips
            AND c2010.county_fips = c2000.county_fips
                AND c2010.p0010001 <> c2000.p0010001
        ORDER BY pct_change ASC
    )
    
    SELECT 
        geo_name,
        state_name,
        MIN(pct_change)
        
    FROM lowest_pct;

The result:

ERROR: ERROR: Column 'low_pct.geo_name' must appear in GROUP BY clause or must be used in an aggregate function
LINE 17: geo_name,
         ^
SQL Status: 42803
Character: 459

How to fix this?

Answer 1

Like Frank already hinted, you need a GROUP BY clause in the outer SELECT.
While being at it, I simplified / fixed a couple of other minor things:

SELECT geo_name, state_name, min(pct_change) AS min_pct_change
FROM  (
   SELECT c2010.geo_name
        , c2010.state_us_abbreviation AS state_name
        , round(((c2010.p0010001 - c2000.p0010001) * 100 / c2010.p0010001)::numeric , 1) AS pct_change
   FROM   us_counties_2010 AS c2010
   JOIN   us_counties_2000 AS c2000 USING (state_fips, county_fips)
   WHERE  c2010.p0010001 <> c2000.p0010001
   ) lowest_pct
GROUP  BY geo_name, state_name  -- !!!
ORDER  BY min_pct_change, state_name, geo_name;

While working with round(expr, 1), there is no need (no point really) to also cast to numeric (8,1) before rounding. The result is type numeric (without modifier) either way.

No need for a CTE. A subquery is cheaper, especially in Postgres 11 or older.

ORDER BY in the CTE (or subquery) is pointless. That order may or may not be carried over after the aggregate in the outer SELECT. I added ORDER BY min_pct_change, state_name, geo_name to the outer SELECT. (You might want ORDER BY min_pct_change DESC ...?)