postgresql: use result from aggregate auxiliary function in select

In an auxiliary function, I use an aggegate function (count) thus it gives only a single result, no column. Later, I want to use the function’s result in my query:

with total as  (select count(*) from employees)
select emp_no, (cast (rank() over (order by emp_no) as Float))/total
from employees

However, this yields an error:

column “total” does not exist

I interpret the error as: the single (aggregate) result need to be converted into a column. How can I solve this? Is there a way to solve this (efficiently)?

I read multiple references about WITH (e.g. https://www.tutorialspoint.com/postgresql/postgresql_with_clause.htm) but there is always a table created in the WITH clause., not a single value as in my case.

Answer

but there is always a table created in the WITH clause., not a single value as in my case.

A CTE always creates a “table”. Even your CTE – it just so happens that your CTE always contains exactly one column and one row.

---

The cause of the error is the fact that your final query doesn’t reference the CTE named total, and therefor it can’t access it (or its column).

You need to include the CTE total in the from clause of your final SELECT statement:

with total as (
  select count(*) as emp_count
  from employees
)
select emp_no, (cast (rank() over (order by emp_no) as Float)) / emp_count
from employees
  cross join total;

You could simplify the expression in the final SELECT, if you cast the count to a number (or float) type:

with total as (
  select count(*)::float as emp_count
  from employees
)
select emp_no, 
       rank() over (order by emp_no) / emp_count
from employees
  cross join total;