Postgresql reference a variable within statement

Question

I come across this scenario frequently in sql and I have seen it done, but would like to summarize the case when/when not to reference a new variable within a statement.

This doesn't work -

SELECT 
COUNT(id) AS total_id,
COUNT(DISTINCT id) AS distinct_id,
total_id - distinct_id AS var
FROM mytable

but this does -

SELECT 
COUNT(id) AS total_id,
COUNT(DISTINCT id) AS distinct_id,
COUNT(id) - COUNT(DISTINCT id) AS var
FROM mytable

How can I implement scenario 1?

You can reference a named variable outside the query [select total_id from (select count(id) as total_id from mytable)z]. It makes sense when you need to, but is not needed in your example above. — Isolated
– Isolated, Commented Feb 10, 2021 at 22:25
very simple use case above - applying to above, how would that look? — paranormaldist
– paranormaldist, Commented Feb 10, 2021 at 22:36
Your misconception might be to think an alias is like a variable in a procedural language. It is not. It just determines the name of the column in the result. — sticky bit
– sticky bit, Commented Feb 10, 2021 at 22:37

Akif Hadziabdic · Accepted Answer · 2021-02-10 22:35:55Z

1

You can use a subquery.

PostgreSQL:
SELECT total_id, distinct_id, (total_id + distinct_id) as var FROM
(
    SELECT COUNT(id) AS total_id, COUNT(DISTINCT id) AS distinct_id
    FROM mytable
) as m

answered Feb 10, 2021 at 22:35

Akif Hadziabdic

2,9181 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paranormaldist Over a year ago

exactly what I was looking for, thank you!

Collectives™ on Stack Overflow

Postgresql reference a variable within statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related