按 2 个字段分组 oracle sql 内连接

Simone 发表于 Dev

西蒙娜

我无法获得正确的语法以能够按两个字段分组：as_of_date 和 ISSUERID。谢谢！

select as_of_date, count(distinct(issuer_id)) from
crd_own.ml_corp_index_data_monthly  tb1
INNER JOIN pm_own.esg_credit_factors tb2 
ON tb1.TICKER = tb2.ISSUER_TICKER
AND trunc(tb1.DATADATE, 'month') = trunc(tb2.AS_OF_DATE, 'month')
where INDEXNAME ='IG' 
and DATADATE = '31-DEC-17'
group by as_of_date, ISSUERID
order by as_of_date asc

保罗麦克斯韦

您在selectandgroup by子句中没有相同数量的“非聚合”列

SELECT
    as_of_date
  , COUNT( DISTINCT (issuer_id) )
...
GROUP BY
    as_of_date
  , ISSUERID     <<< this is the problem

您需要在 select 子句中包含 ISSUERID：

SELECT
    as_of_date
  , ISSUERID
  , COUNT( DISTINCT (issuer_id) )
...
GROUP BY
    as_of_date
  , ISSUERID
ORDER BY
    as_of_date ASC

或者完全删除ISSUERID。

SELECT
               -- non-aggregating columns
    as_of_date
  , ISSUERID

              -- aggregating columns
  , COUNT( DISTINCT (issuer_id) )

FROM ...
WHERE ...
GROUP BY
              -- repeat all non-aggregating columns here
    as_of_date
  , ISSUERID

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。