CODEWITHSUNDEEP
sqlpostgresql
Sebastian
Sebastian

Reputation: 967

How can I use columns I create in my select clause?

I'm curious if it's possible to use columns I create in my select clause for other fields.

For example, I have this query -- 

SELECT 
    users.officeid,
    COUNT(DISTINCT CASE WHEN users.active = 1 THEN user.id ELSE NULL END) as active_users,
    COUNT(CASE WHEN users.total = 1  THEN 1 ELSE NULL END) as total_users,
    active_users / total_users

FROM users  
GROUP BY 1

I would like to have another column that is active_users / total_users, but I get the error -->

column "active_users" does not exist in users

Do I need to put these in a common table expression and then select from them? Is there another solution? Thanks!

Upvotes: 0

Views: 47

Answers (3)

A.S.
A.S.

Reputation: 320

How about this?

select officeid, active_users, total_users, active_users / total_users
from
(
SELECT 
    users.officeid,
    COUNT(DISTINCT CASE WHEN users.active = 1 THEN user.id ELSE NULL END) as active_users,
    COUNT(CASE WHEN users.total = 1  THEN 1 ELSE NULL END) as total_users
FROM users  
GROUP BY 1
)

Upvotes: 1

Jakob
Jakob

Reputation: 257

"As ..." only renames the column for the query output.

If you want to calculate this on the go and avoid creating an additional column you can do the following:

select count(column1), count(column1), 
count(column1) / count(column2)
from table

You could also create a materialized view for it.

Upvotes: 2

cCcik
cCcik

Reputation: 107

with cccik as( SELECT 
    users.officeid,
    COUNT(DISTINCT CASE WHEN users.active = 1 THEN user.id ELSE NULL END) as active_users,
    COUNT(CASE WHEN users.total = 1  THEN 1 ELSE NULL END) as total_users,


FROM users  
GROUP BY 1)
select *, (active_users / total_users) from ccckik 
-- you can filter by officeid
-- where officeid = 5

Upvotes: 2

Related Questions