SQL ORA-00937:不是单组组函数错误

Question

我正在努力查看为什么Oracle不让我运行此操作并给我一个ORA-00937：不是单组组函数"错误。我有我的小组，这是唯一的专栏。也许我做错了？帮助？预先感谢

显示公司的姓名和贸易量的公司，其股票在全球股东的总股东交易最多。

SELECT MAX(COUNT(trade.shares)) AS "Max Count of Company Stock",
       company.name
FROM   trade
       JOIN company
            ON  company.stock_id = trade.stock_id
GROUP BY company.name;

Answer 1

如果我了解您要完成的工作，以下内容应该使您到达那里：

WITH cteCompany_shares AS (SELECT c.NAME, SUM(t.SHARES) AS SHARES_TRADED
                             FROM COMPANY c
                             INNER JOIN TRADE t
                               ON t.STOCK_ID = c.STOCK_ID
                             GROUP BY c.NAME)
SELECT cs.NAME, cs.SHARES_TRADED
  FROM cteCompany_shares cs
  WHERE cs.SHARES_TRADED = (SELECT MAX(SHARES_TRADED)
                              FROM cteCompany_shares);

Answer 2

首先，我认为您要sum分享，而不是count。

问题是您的查询正在尝试一次执行两个级别的聚合(首先计算/总和股票，然后以最大值为单位(，这是不可能的。

尝试以下操作：

select c.name
     , count(t.shares) as "Number of trades"
     , sum(t.shares) as "Trade volume"
from   trade t
       join company c on c.stock_id = t.stock_id
group by c.name
order by sum(t.shares) desc
fetch first row only;

(fetch first子句需要Oracle 12.1或更高版本。(

或以下：

select name, total_shares
from   ( select c.name
              , sum(t.shares) as total_shares
              , rank() over (order by sum(t.shares) desc) as ranking
         from   trade t
                join company c on c.stock_id = t.stock_id
         group by c.name )
where  ranking = 1;

样本数据：

create table company
( stock_id  number primary key
, name      varchar2(30) not null );
create table trade
( stock_id  references company
, shares    number not null );
insert all
    into company values (1, 'Apple')
    into company values (2, 'Microsoft')
    into company values (3, 'Oracle')
    into company values (4, 'Huawei')
    into company values (5, 'Robertson Solutions')
select * from dual;
insert all
    into trade values (1,  10)
    into trade values (2,   5)
    into trade values (3, 100)
    into trade values (4, 200)
    into trade values (5,   5)
    into trade values (1,  20)
    into trade values (2,  30)
    into trade values (3,  40)
    into trade values (4,  50)
    into trade values (5,  20)
    into trade values (1,  70)
select * from dual;

汇总数据：

select c.name
     , sum(t.shares) as total_shares
     , rank() over (order by sum(t.shares) desc) as ranking
from   trade t
       join company c  on  c.stock_id = t.stock_id
group by c.name
order by total_shares desc;
NAME                 TOTAL_SHARES    RANKING
-------------------- ------------ ----------
Huawei                        250          1
Oracle                        140          2
Apple                         100          3
Microsoft                      35          4
Robertson Solutions            25          5