Is it possbile to follow DRY principal in PostgreSQL inside select statement

Question

SELECT
    C.category_name,
    P.product_name,
    SUM(P.unit_price) as unit_price,
    ROUND(
        AVG(P.unit_price) OVER(partition by C.category_name) :: numeric,
        2
    ) AS avg_unit_price,
    ROUND(
        (
            (
                MAX(P.unit_price) OVER(partition by C.category_name) + MIN(P.unit_price) OVER(partition by C.category_name)
            ) / 2
        ) :: numeric,
        2
    ) AS median_unit_price,
    CASE
        WHEN SUM(P.unit_price) < (
            AVG(P.unit_price) OVER(partition by C.category_name)
        ) THEN 'BELOW AVERAGE'
        WHEN SUM(P.unit_price) > (
            AVG(P.unit_price) OVER(partition by C.category_name)
        ) THEN 'OVER AVERAGE'
        WHEN SUM(P.unit_price) = (
            AVG(P.unit_price) OVER(partition by C.category_name)
        ) THEN 'AVERAGE'
    END AS avg_unit_price,
    CASE
        WHEN SUM(P.unit_price) < (
            (
                MAX(P.unit_price) OVER(partition by C.category_name) + MIN(P.unit_price) OVER(partition by C.category_name)
            ) / 2
        ) THEN 'BELOW MEDIAN'
        WHEN SUM(P.unit_price) > (
            (
                MAX(P.unit_price) OVER(partition by C.category_name) + MIN(P.unit_price) OVER(partition by C.category_name)
            ) / 2
        ) THEN 'OVER MEDIAN'
        WHEN SUM(P.unit_price) = (
            (
                MAX(P.unit_price) OVER(partition by C.category_name) + MIN(P.unit_price) OVER(partition by C.category_name)
            ) / 2
        ) THEN 'MEDIAN'
    END AS median_unit_price
FROM
    products as P
    JOIN categories as C USING(category_id)
WHERE
    P.discontinued = 0
GROUP BY
    P.product_name,
    C.category_name,
    p.unit_price
ORDER BY
    C.category_name ASC,
    C.category_name ASC;

in above query I have used this window function to get median

 (MAX(P.unit_price) OVER(partition by C.category_name) + MIN(P.unit_price) OVER(partition by C.category_name)) / 2

Which I have later used in CASE statement for comparison, I need to copy paste same code over and over again is there a positble solution for this? I did some reasearch, didnot help as I am new to postgres ,so I need help.

You mention MySQL in your title, then you mention Postgres in the last sentence and you tag it both [tag:mysql] and [tag:postgresql]. But I see some PostgreSQL-specific syntax in the query. Which database are you really using? What does `SELECT VERSION()` return? — Bill Karwin, Oct 05 '22 at 20:05

score 1 · Answer 1 · answered Oct 05 '22 at 20:14

In general, you would have to repeat the expression if you want to use it multiple times in the select-list of the same query. You can create a column alias, but standard SQL (and both MySQL and PostgreSQL) do not allow a column alias to be used in another expression in the same select-list. The reason is that the order of evaluating expressions in the select-list is not defined, and is not guaranteed to be left-to-right.

select 1 as x, 2 + x;

ERROR:  column "x" does not exist
LINE 1: select 1 as x, 2 + x;
                           ^

But you can use a subquery to define column aliases, then reference them in an outer query.

select 2 + x from (select 1 as x) as t;

 ?column? 
----------
        3
(1 row)

This is the way to practice DRY as you say. Define an expression in a derived table subquery, then use it as many times as you want in the outer query.

Is it possbile to follow DRY principal in PostgreSQL inside select statement

1 Answers1