CODEWITHSUNDEEP
mysql
user1535808
user1535808

Reputation: 21

MySql order by COUNT and list the total count

I am using this code to count and sort from my database:

$qry = "select entertainer, count(*) from final_results group by entertainer order by count(*) desc";

I get the right result, in as much as it lists all the contents in order or popularity.

What I would like is the top 5 results to display, with a count for each of them and then a total count of all the results.

e.g.

Top response (10)
Second response (8)
Third response (6)
etc...

Total count = 56

I would appreciate any help and advice.

Thanks,

John C

Upvotes: 2

Views: 2455

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171529

select entertainer, count
from (
    (select entertainer, count(*) as count, 1 as sort
    from final_results 
    group by entertainer 
    order by count(*) desc
    limit 5)

    union all 

    (select 'Total count', (select count(*) from final_results), 2 as sort)
) a 
order by sort, count desc

Upvotes: 2

MvG
MvG

Reputation: 60988

You can get the totaly using WITH ROLLUP, but that won't work well with the LIMIT 5 you want in order to only fetch the top five results. (Edit:) It will also not work with the ordering, as discussed in the comments.

So you'll either have to fetch all results, not just the top 5, and sort them in the application, or use two distinct queries, possibly merged on the server side using UNION the way @RedFilter suggests. But if you do two separate queries, the I personally would rather issue each one separately from the client application, as splitting the total from the top five later on is too much work for little gain.

To fetch all results, you'd use

select entertainer, count(*)
from final_results
group by entertainer with rollup

To do two distinct fetches you'd use

select entertainer, count(*)
from final_results
group by entertainer
order by count(*) desc
limit 5

and

select count(*)
from final_results

If you want both in a single union, you can do this as

(select 1 as unionPart, entertainer, count(*) as count
 from final_results
 group by entertainer
 order by count(*) desc
 limit 5)
union all
(select 2 as unionPart, 'Total count', count(*) as count
 from final_results)
order by unionPart asc, count desc

Upvotes: 3

Related Questions