Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Count by occurrences

I have a query similar to:

select * 
from combined 
where valid_rating is null
   order by FIND_IN_SET(provider, 
    (select provider 
     from combined 
     where valid_rating is null group by provider order by count(1) desc)
   )

Basically, I am trying to order by the ‘most important’ provider first (the one with the largest count of items). How would I properly do this with an expression in the order_by clause, perhaps even using a window function?

Tags:

2

Answers


  1. 0

    Join with the subquery that returns the counts, then order by the counts.

    SELECT c.*, ct.count
FROM combined AS c
JOIN (
    SELECT provider, COUNT(*) AS count
    FROM provider
    GROUP BY provider
) AS ct ON c.provider = ct.provider
ORDER BY count DESC
    Login or Signup to reply.
  2. 0

    Use count() window function, partitioned by provider:

    select c.*, count(*) over (partition by provider) cnt
from combined c
where valid_rating is null
order by cnt desc
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search