SQL aggregate function subquery

aggregate, count, rows, sql, subquery

Solution

Try this

select pp.prop_id, 
      (select COUNT(employee_id) 
       from employee e 
       where e.ao1_hours > 0 and e.employee_id = pp.employee_id) as nb_employees
from proposal_piece pp      
order by pp.prop_id

or this

select pp.prop_id, count(e.employee_id) as nb_employees  
from proposal_piece pp inner join employee e 
     on pp.employee_id = e.employee_id
where e.ao1_hours > 0
group by pp.prop_id
order by pp.prop_id

Problem

What I want to do is count the number of rows returned by a subquery, essentially the following: ``` select pp.prop_id, COUNT((select employee_id from employee e where e.ao1_hours > 0)) from proposal_piece pp group by pp.prop_id order by pp.prop_id ``` Here is my error message: ``` Cannot perform an aggregate function on an expression containing an aggregate or a subquery. ``` Why does this not work? If select is just returning a bunch of `employee_id's` with a filtering criteria why can't I count the number of rows or `employee_id's` that are being returned? I am looking to count the number of distinct employees that have `ao1_hours > 0`. Grouped by the `prop`. Here is some structural information about my database, as part of a query. ``` from proposal_piece pp INNER JOIN employee e on pp.employee_id = e.employee_id ``` Thanks!

Original source