Use column named using 'AS' clause in next column definition

Question

How would I use the column I defined as 'total_affected' in place of the long function?.. I feel like there is a better way to do this rather than to repeat the same thing from the previous line. I am not sure how to define it so I can use it right after in the next column.

SELECT c.name,
       COALESCE(sum(country_status_count.cured), 0) as cured,
       COALESCE(sum(country_status_count.dead), 0) as dead,
       cic.infected,
       SUM(COALESCE(cured, 0) + COALESCE(dead, 0) + infected) AS total_affected,
       (total_affected / c.population) as rate_of_infection <-- Something like this line instead of copying the whole function from the line above


FROM country_status_count

RIGHT JOIN country c ON country_status_count.country_id = c.id
RIGHT JOIN country_infection_counts cic ON c.name = cic.name

GROUP BY c.name, cic.infected, c.population
ORDER BY c.name ASC;

J Spratt · Accepted Answer · 2020-04-16T14:33:00.407

You need to use either a CTE to set the dataset before applying the division or duplicate the SUM() clause in the division.

WITH dataset AS
(
    SELECT c.name
         , COALESCE(sum(country_status_count.cured), 0) as cured
         , COALESCE(sum(country_status_count.dead), 0) as dead
         , cic.infected
         , SUM(COALESCE(cured, 0) + COALESCE(dead, 0) + infected) AS total_affected
         , c.population
      FROM country_status_count
     RIGHT JOIN country c ON country_status_count.country_id = c.id
     RIGHT JOIN country_infection_counts cic ON c.name = cic.name
     GROUP BY c.name, cic.infected
     ORDER BY c.name ASC;
)
SELECT *
     , (total_affected / population) as rate_of_infection <-- Something like this line
  FROM dataset
;

Or:

SELECT c.name
     , COALESCE(sum(country_status_count.cured), 0) as cured
     , COALESCE(sum(country_status_count.dead), 0) as dead
     , cic.infected
     , SUM(COALESCE(cured, 0) + COALESCE(dead, 0) + infected) AS total_affected
     , (SUM(COALESCE(cured, 0) + COALESCE(dead, 0) + infected)/ c.population) as rate_of_infection
  FROM country_status_count
 RIGHT JOIN country c ON country_status_count.country_id = c.id
 RIGHT JOIN country_infection_counts cic ON c.name = cic.name
 GROUP BY c.name, cic.infected, c.population
 ORDER BY c.name ASC
;

score 1 · Answer 2 · answered Apr 16 '20 at 14:22

Add the column c.population in the GROUP BY clause and reuse SUM(COALESCE(cured, 0) + COALESCE(dead, 0) + infected) for the calculation, because you can't use the derived column total_affected:

SELECT c.name,
       COALESCE(sum(country_status_count.cured), 0) as cured,
       COALESCE(sum(country_status_count.dead), 0) as dead,
       cic.infected,
       SUM(COALESCE(cured, 0) + COALESCE(dead, 0) + infected) AS total_affected,
       (SUM(COALESCE(cured, 0) + COALESCE(dead, 0) + infected) / c.population) as rate_of_infection 
FROM country_status_count
RIGHT JOIN country c ON country_status_count.country_id = c.id
RIGHT JOIN country_infection_counts cic ON c.name = cic.name
GROUP BY c.name, c.population, cic.infected
ORDER BY c.name ASC;

Use column named using 'AS' clause in next column definition

2 Answers2