How to execute a Postgresql subquery in a select clause with an introduction from a SQL Server type clause?

Question

I am trying to write the following query in postgresql:

select name, author_id, count(1), (select count(1) from names as n2 where n2.id = n1.id and t2.author_id = t1.author_id ) from names as n1 group by name, author_id 

This will certainly work on Microsoft SQL Server, but it is not at all on postegresql. I read its documentation a bit and it seems I can rewrite it as:

 select name, author_id, count(1), total from names as n1, (select count(1) as total from names as n2 where n2.id = n1.id and n2.author_id = t1.author_id ) as total group by name, author_id 

But this returns the following error in postegresql: "A subquery in FROM cannot refer to other relations of the same query level." So I'm stuck. Does anyone know how I can achieve this?

thank

Answer 1

I'm not sure I understand your intention perfectly, but perhaps the following will be close to what you want:

 select n1.name, n1.author_id, count_1, total_count from (select id, name, author_id, count(1) as count_1 from names group by id, name, author_id) n1 inner join (select id, author_id, count(1) as total_count from names group by id, author_id) n2 on (n2.id = n1.id and n2.author_id = n1.author_id) 

Unfortunately, this adds the requirement of grouping the first subquery by id, as well as the name and author_id, which, it seems to me, are not needed. I'm not sure how to get around this, though, since you need to have an identifier available for joining in the second subquery. Maybe someone else will come up with a better solution.

Share and enjoy.

Answer 2

I just answer here with a formatted version of the latest sql file that I need based on Bob Jarvis's answer as stated in my comment above:

 select n1.name, n1.author_id, cast(count_1 as numeric)/total_count from (select id, name, author_id, count(1) as count_1 from names group by id, name, author_id) n1 inner join (select author_id, count(1) as total_count from names group by author_id) n2 on (n2.author_id = n1.author_id)