SQL ORA-00937: not a single-group group function error

Question

I'm struggling to see why Oracle isn't letting me run this and giving me a "ORA-00937: not a single-group group function" error. I have my GROUP BY and the only column that should be in it. Maybe I did the query wrong? Help? Thanks in advance

Display the name of the company and trade volume for the company whose stock has the largest total volume of shareholder trades worldwide.

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

First of all, I think you want to sum the shares, not count them.

The issue is that your query is trying to perform two levels of aggregation at once (first count/sum the shares, then take the max of that), which is not possible.

Try this:

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;

(The fetch first clause requires Oracle 12.1 or later.)

Or this:

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;

Sample data:

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;

Aggregated data:

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

Answer 2

If I understand what you're trying to accomplish, the following should get you there:

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);