Question 247787 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Refactoring repeated expressions in SQL query

I have the following PostgreSQL query:

SELECT extract(YEAR from date), AVG(amount) 
FROM transactions 
GROUP BY extract(YEAR from date) 
ORDER BY extract(YEAR from date)

How can I refactor the query so that extract(YEAR from date) does not have to be repeated in the query?

Tags:

3

Answers


  1. 0

    I’m not sure if it is more readable or not, but you can refer to the output column ID:

    SELECT extract(YEAR from date), AVG(amount) 
FROM transactions 
GROUP BY 1
ORDER BY 1
    Login or Signup to reply.
  2. 0

    Can use a common table expression.

    WITH year_avg AS (
  SELECT extract(YEAR from date) AS transaction_year, AVG(amount) AS avg_amount
  FROM transactions
  GROUP BY transaction_year
)

    This will create a CTE for you and then you can later, call it using

    SELECT transaction_year, avg_amount
FROM year_avg
ORDER BY transaction_year;

    The main query selects the results from the CTE and orders them by transaction_year, doing this, you don’t need to repeat the extract(YEAR from date) expression in the main query and the year_avg CTE calculates the average amount for each year by extracting the year from the date column and grouping it.

    Login or Signup to reply.
  3. 0

    You can do it using subquery as follows :

    SELECT *
FROM (
  SELECT extract(YEAR from date) as year, AVG(amount) AS avg_amount
  FROM transactions 
  GROUP BY extract(YEAR from date)
)
ORDER BY year;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search