SQL聚合函数子查询 [英] SQL aggregate function subquery

问题描述

我想要做的是计算子查询返回的行数，基本上是以下内容：

  select pp。 prop_id，COUNT（（从雇员e中选择e.ao1_hours> 0的employee_id））
来自proposal_piece pp 
 
由pp.prop_id 
组按pp.prop_id 
  

这是我的错误消息：

 无法对包含聚集或子查询的表达式执行聚集功能。 
  

为什么这不起作用？如果select仅返回一堆带有过滤条件的 employee_id's ，为什么我不能计算行数或 employee_id的

我正在计算具有 ao1_hours> 0 。按 prop 分组。

以下是有关我数据库的一些结构信息，作为查询的一部分。

  from proposal_piece pp 
 INNER JOIN员工e 
 on pp.employee_id = e.employee_id 
  

谢谢！

解决方案

尝试这个

 选择pp.prop_id，
（从员工e $中选择COUNT（employee_id）
 b $ b，其中e.ao1_hours> 0且e.employee_id = pp.employee_id）为nb_employees 
，来自pp.prop_id 
的proposal_piece pp 
订单pre> 
 
 
或此
 选择pp.prop_id，count（e .employee_id）为nb_employees 
，来自proposal_piece pp内部加入员工e e 
 on pp.employee_id = e.employee_id 
其中e.ao1_hours> 0 
由pp.prop_id 
分组pp.prop_id 
  
 
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!
解决方案
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


                        
这篇关于SQL聚合函数子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！