How to do a Postgresql subquery in select clause with join in from clause like SQL Server?
- by Ricardo
I am trying to write the following query on 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 would certainly work on Microsft SQL Server but it does not at all on postegresql. I read its documentation a bit and it seems I could 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 that returns the following error on postegresql: "subquery in FROM cannot refer to other relations of same query level". So I'm stuck. Does anyone know how I can achieve that?
Thanks
