使用Oracle时，子查询中出现奇怪的“ GROUP BY”错误

|| 快速背景-我需要找到通过我们公司实验室进行的案件终止率，按案件类型和月份分组。到目前为止，我想到了这个： 
SELECT BPI.TYPE,
       EXTRACT(MONTH FROM CS.RECEIVED_DATE) MONTH,
       COUNT(*) termed_cases
  FROM CELL_SOURCE cs
  JOIN BASIC_PATHOLOGY_INFO bpi ON CS.CELL_SOURCE_ID = BPI.CELL_SOURCE_ID
  JOIN RECENT_CELL_SOURCE_STATUS rcss ON CS.CELL_SOURCE_ID = RCSS.CELL_SOURCE_ID
 WHERE type IS NOT NULL
  AND CS.RECEIVED_DATE > to_date(\'03/01/2011\', \'MM/DD/YYYY/\')
  AND RCSS.STATUS like \'Term%\'
GROUP BY BPI.TYPE, EXTRACT(MONTH FROM CS.RECEIVED_DATE)
ORDER BY month, type
这样就可以轻松找到所有已命名的案例。但是，当我想找到费率时，会遇到一些问题。我尝试使用子查询来捕获每种类型的总病例数，无论其状态如何，例如： 
COUNT(*)/(SELECT COUNT(*) 
            FROM CELL_SOURCE cs_1 
            JOIN BASIC_PATHOLOGY_INFO bpi_1 ON CS_1.CELL_SOURCE_ID = BPI_1.CELL_SOURCE_ID
          WHERE BPI_1.TYPE = BPI.TYPE 
            AND EXTRACT(month from CS_1.RECEIVED_DATE) = EXTRACT(MONTH FROM CS.RECEIVED_DATE)) termed_cases
但是，这将引发
ORA-00979: not a GROUP BY expression
错误，并突出显示子查询中的
BPI.TYPE
。 有人知道我的错误可能是什么吗？另外，在这里，解析函数比汇总函数能更好地工作吗？     
2020-01-09 3 条评论
分享

没有找到相关结果

    已邀请:

    翁茄口霉氖

    因此，您需要两个计数：一个月中所有案件的总数和仅Termated案件的总数。最简单的方法是使用CASE（）函数执行条件计数，如下所示： 
    SELECT BPI.TYPE,
       EXTRACT(MONTH FROM CS.RECEIVED_DATE) MONTH,
       COUNT(*) all_cases,
       sum(case when RCSS.STATUS like \'Term%\' then 1 else 0 end ) termed_cases
  FROM CELL_SOURCE cs
  JOIN BASIC_PATHOLOGY_INFO bpi ON CS.CELL_SOURCE_ID = BPI.CELL_SOURCE_ID
  JOIN RECENT_CELL_SOURCE_STATUS rcss ON CS.CELL_SOURCE_ID = RCSS.CELL_SOURCE_ID
 WHERE tumor_type IS NOT NULL
  AND CS.RECEIVED_DATE > to_date(\'03/01/2011\', \'MM/DD/YYYY/\')
GROUP BY BPI.TUMOR_TYPE, EXTRACT(MONTH FROM CS.RECEIVED_DATE)
ORDER BY month, tumor_type
    请注意，我已经从WHERE子句中删除了LIKE过滤器。     
    2020-01-09 0 0
    分享

    皇小福另届 

    select bpi.type , month, 
       termed_cases / 
   (select count(*) 
    from CELL_SOURCE cs_1 
    inner join BASIC_PATHOLOGY_INFO bpi_1 
        on CS_1.CELL_SOURCE_ID = BPI_1.CELL_SOURCE_ID
    where BPI_1.TUMOR_TYPE = BPI.TUMOR_TYPE 
    and extract(month from CS_1.RECEIVED_DATE) = extract(MONTH FROM CS.RECEIVED_DATE)
    )
from (
select BPI.TYPE,
extract(MONTH FROM CS.RECEIVED_DATE) MONTH,
count(*) termed_cases
from CELL_SOURCE cs
inner join BASIC_PATHOLOGY_INFO bpi
    on CS.CELL_SOURCE_ID = BPI.CELL_SOURCE_ID
inner join RECENT_CELL_SOURCE_STATUS rcss
    on CS.CELL_SOURCE_ID = RCSS.CELL_SOURCE_ID
where tumor_type is not null
and CS.RECEIVED_DATE > to_date(\'03/01/2011\', \'MM/DD/YYYY/\')
and RCSS.STATUS like \'Term%\'
group by BPI.TYPE, extract(MONTH FROM CS.RECEIVED_DATE)
)
order by month, type
        
    2020-01-09 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.071025133132935