Mysql count on ORDERBY not working with Join-CodePudding

I have two tables "coin_review" and "un_user_follower",And i want to fetch "Top Reviewer"(except me(123),And whose Most Review exist,with his follower) Here is my table "coin_review"

id          Review              wallet_address      
1           Lorem Ipsum1        123 
2           Lorem Ipsum2        1234
3           Lorem Ipsum3        1234
4           Lorem Ipsum4        12345

...

Here is my table "un_user_follower"

id          from_wallet_address         to_wallet_address       
1           1234                            11111       
2           1234                            12254
3           123                             25141
4           12345                           14144
5           12345                           14798
...

Now as result i want to Get Top Reviewer (Whose most record exist) and Total number of his follower For example i want following output as result

id          wallet_address          TotalReview         TotalFollowers
1           1234                        2                   2
2           12345                       1                   1

For this i tried with following query

SELECT cr.id,COUNT(cr.Review) as Total,COUNT(usf.to_wallet_address)
FROM coin_review cr
JOIN un_user_follower usf
ON usf.to_wallet_address=cr.wallet_address
WHERE cr.wallet_address!='123'
GROUP BY cr.Review
ORDER BY Total ASC

But i am getting following errror,How can i solve this ?Where i am wrong ?

SELECT list is not in GROUP BY clause and contains nonaggregated column 'Uni_back.cr.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

CodePudding user response：

Top two reviewers and their followers count. First group tables then LEFT JOIN.

SELECT cr.wallet_address, cr.TotalReview, coalesce(fl.TotalFollowers,0) TotalFollowers
FROM (
  SELECT wallet_address, COUNT(Review) as TotalReview
  FROM coin_review 
  WHERE wallet_address!='123'
  GROUP BY wallet_address
  ORDER BY COUNT(Review) DESC
  LIMIT 2 
)  cr
LEFT JOIN (
  SELECT from_wallet_address,  COUNT(to_wallet_address) TotalFollowers
  FROM  un_user_follower
  WHERE from_wallet_address!='123'
  GROUP BY from_wallet_address
) fl ON cr.wallet_address = fl.from_wallet_address
ORDER BY cr.TotalReview ASC

db<>fiddle

returns

wallet_address  TotalReview TotalFollowers
12345   1   2
1234    2   2