Aggregated SQL aggregate alias

Question

I'm starting in SQL, and this is the question that I was asked to solve:

Say that a large city is defined as a placetype citywith a population of at least 100,000. Write an SQL query that returns a scheme (state_name,no_big_city,big_city_population)ordered by state_namelisting those states that have either (a) at least five major cities, or (b) at least million people living in big cities. The column state_nameis nameof state, no_big_city- the number of big cities in the state, and big_city_population- the number of people living in big cities in the state.

Now, as far as I can see, the following query returns the correct results:

SELECT state.name AS state_name
     , COUNT(CASE WHEN place.type = 'city' AND place.population >= 100000 THEN 1 ELSE NULL END) AS no_big_city
     , SUM(CASE WHEN place.type = 'city' AND place.population >= 100000 THEN place.population ELSE NULL END) AS big_city_population
FROM state
JOIN place
ON state.code = place.state_code
GROUP BY state_name
    HAVING
        COUNT(CASE WHEN place.type = 'city' AND place.population >= 100000 THEN 1 ELSE NULL END) >= 5 OR
        SUM(CASE WHEN place.type = 'city' AND place.population >= 100000 THEN place.population ELSE NULL END) >= 1000000
ORDER BY state_name;

, , . : ?

, , "column not exist".

Answer 1

:

ORDER BY GROUP BY, WHERE HAVING; .

.

CTE:

SELECT state_name, no_big_city, big_city_population
FROM  (
   SELECT s.name AS state_name
        , COUNT(*)        FILTER (WHERE p.type = 'city' AND p.population >= 100000) AS no_big_city
        , SUM(population) FILTER (WHERE p.type = 'city' AND p.population >= 100000) AS big_city_population
   FROM   state s
   JOIN   place p ON s.code = p.state_code
   GROUP  BY s.name -- can be input column name as well, best schema-qualified to avoid ambiguity
   ) sub
WHERE  no_big_city >= 5
   OR  big_city_population >= 1000000
ORDER  BY state_name;

FILTER (Postgres 9.4 +):

• ?

:

SELECT s.state_name, p.no_big_city, p.big_city_population
FROM   state s
JOIN  (
   SELECT state_code      AS code  -- alias just to simplify join
        , count(*)        AS no_big_city
        , sum(population) AS big_city_population
   FROM   place
   WHERE  type = 'city'
   AND    population >= 100000
   GROUP  BY 1  -- can be ordinal number referencing position in SELECT list
   HAVING count(*) >= 5 OR sum(population) >= 1000000  -- simple expressions now
   ) p USING (code)
ORDER  BY 1;    -- can also be ordinal number

GROUP BY ORDER BY. , .

Answer 2

, , , ,

, , ( , LOT ), , , . ANSI-SQL , PostGRES

select * from (

SELECT STATE.NAME AS state_name
    ,COUNT(CASE WHEN place.type = 'city'
                AND place.population >= 100000 THEN 1 ELSE NULL END) AS no_big_city
    ,SUM(CASE WHEN place.type = 'city'
                AND place.population >= 100000 THEN place.population ELSE NULL END) AS big_city_population
FROM STATE
INNER JOIN place
    ON STATE.code = place.state_code
    GROUP BY state_name
) sub 
    where no_big_city >= 5 
        and big_city_population >=100000

--HAVING COUNT(CASE WHEN place.type = 'city'
--          AND place.population >= 100000 THEN 1 ELSE NULL END) >= 5
--  OR SUM(CASE WHEN place.type = 'city'
--              AND place.population >= 100000 THEN place.population ELSE NULL END) >= 1000000
ORDER BY state_name;

, , /, , . , , , , .

Answer 3

SELECT - , WHERE.
GROUP BY - , SELECT. , . . - :

SELECT state_name, no_big_city, big_city_population 
FROM
 (
   SELECT 
     state.name AS state_name,     
     COUNT(1) no_big_city,
     MAX(place.population) max_city_population,
     SUM(place.population) AS big_city_population
   FROM state JOIN place ON state.code = place.state_code 
   WHERE   
     place.type = 'city' AND
     place.population >= 100000
   GROUP BY  state.name
  )
WHERE 
   no_big_city >= 5 OR
   max_city_population > 1000000
ORDER BY state_name

,

   place.type = 'city' AND
   place.population >= 100000

CASE . " " " " , place.type .