CODEWITHSUNDEEP
mysqlselectcountmaxsql-order-by
ABpositive
ABpositive

Reputation: 303

MySQL select max count from column

im trying to select the broker_id with highest number of trades from the trades table conducted by the broker_id.

Originally i tried the following:

SELECT MAX ( count ( trade_id ) ) FROM trades GROUP BY broker_id; 

Error Code: 1630. FUNCTION brokerdb.MAX does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

but apparently this is only supported in oracle?

I then tried modifying a suggested post from a friend in the following:

SELECT broker_id FROM trades WHERE trade_id =(SELECT max(count(trade_id)) FROM trades) group by broker_id;

but produces a group by error

Error Code: 1111. Invalid use of group function

Could you people either tell me what im doing wrong or a potential solution?

Thanks

Upvotes: 0

Views: 86

Answers (1)

Dark Knight
Dark Knight

Reputation: 6531

SELECT broker_id, COUNT(trade_id) cnt
FROM trades 
GROUP BY broker_id
ORDER BY cnt DESC
LIMIT 1

Upvotes: 1

Related Questions