PostgreSQL subquery as usable variable

Question

I'm trying to use a subquery that comes out into a column in another calculation later on. How can I do this?

SELECT c_id, c_title, c_enrolcap,
(SELECT COUNT(e_id) AS enrol FROM enrollments WHERE e_c_id = c_id) AS enrolled,
c_enrolcap - enrolled AS avail,
FROM classes AS c

So basically what comes out of enrolled I need this as a column to calculate off of later and also as it's own column.

Is the above query expected to return a single row or multiple rows? — Eric Hauenstein
– Eric Hauenstein, Commented Oct 3, 2013 at 20:00

Eric Hauenstein · Accepted Answer · 2013-10-03 20:12:32Z

12

I usually do this with Common Table Expressions.

http://www.postgresql.org/docs/9.3/static/queries-with.html

Do the c_enrolcap - enrolled as avail in the outer query.

Like:

WITH enrollment AS (
SELECT c_id, c_title, c_enrolcap,
(SELECT COUNT(e_id) AS enrol FROM enrollments WHERE e_c_id = c_id) AS enrolled

FROM classes AS c)
SELECT c_id, c_title, c_enrolcap, enrolled, c_enrolcap - enrolled AS avail
FROM enrollment

answered Oct 3, 2013 at 20:12

Eric Hauenstein

2,5656 gold badges35 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL subquery as usable variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related