MySQL display a count of rows for 2 Group Bys

Question

Sorry in advance, I'm not exactly sure how to explain this but I am trying to do this:

I have a table that looks something like this:

id | make | model

1 | iphone | iphone 7
2 | iphone | iphone 8
3 | iphone | iphone 10
4 | samsung | galaxy note 5
5 | samsung | galaxy note 5
6 | samsung | galaxy note 4
7 | iphone | iphone 7
8 | iphone | iphone 7
9 | htc | one

I want the results to look like this:

htc | one | 1 
iphone | iphone 7 | 3 
samsung | galaxy note 5 | 2

So essentially I only want the count for the most rows for each model while grouping both the make and the model.

Hope that makes some sort of sense to someone. Thank you in advance!

this should get you what you want: https://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement — Naytzyrhc, Nov 23 '17 at 23:42
Possible duplicate of [How to use count and group by at the same select statement](https://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) — herrbischoff, Nov 23 '17 at 23:50
oh shoot sorry my bad there was a typo the first record should of been iphone 7. — VinceLovesChickenAndBeer, Nov 24 '17 at 01:55
Thank you everyone who gave some thought and input into this much appreciated! — VinceLovesChickenAndBeer, Nov 24 '17 at 02:57
If any of the answers below has solved/helped you to solve your problem,kindly mark it as an answer and/or upvote the answer. — Harshil Doshi, Dec 06 '17 at 19:57

Harshil Doshi · Answer 1 · 2017-11-24T01:40:58.453

1

So you want to display the model with Maximum count for each make.

Following query will just do that:

select make,model,count(*) as cnt
from phones p1
group by make,model
having cnt =  (select max(p2.cnt) 
               from 
                   (select make,model,count(*) as cnt
                          from phones
                          group by make,model
                   )p2
                where p2.make = p1.make                
               )
 ;

Click here for updated DEMO

Hope it helps!

edited Nov 24 '17 at 01:40

answered Nov 24 '17 at 00:52

Harshil Doshi

3,497
3
14
37

@Strawberry Can you please explain? – Harshil Doshi Nov 24 '17 at 01:09
@Strawberry Yeah, i come to know that. Thank you for pointing it out. Although I've updated my answer. Please have a look. – Harshil Doshi Nov 24 '17 at 01:41
Yeah, I think that's ok - although I'm not sure that it scales so well. – Strawberry Nov 24 '17 at 01:44

Blank · Answer 2 · 2017-11-24T02:23:17.380

Do some tricks with mysql functions, you can use substring_index and group_concat:

select 
  `make`,
  substring_index(group_concat(`model` order by cnt desc), ',', 1) `model`,
  max(cnt) max_count
from (
  select `make`, `model`, count(1) cnt
  from yourtable
  group by `make`, `model`) t
group by `make`

See demo in SQLFiddle.

Some explaination:

Subquery t will query count of each group make, model;
group_concat(model order by cnt desc) with group by make will let all model be concated with separator , and order by cnt desc;
substring_index is a function which can split string with specified separator and return sub-string according to the index we passed.

All these functions you can check mysql official documents to learn more information.

score 0 · Answer 3 · answered Nov 24 '17 at 01:27

You're probably after something like this:

SELECT a.* 
  FROM 
     ( SELECT make
            , model
            , COUNT(*) total 
         FROM my_table 
        GROUP 
           BY make
            , model
     ) a
  JOIN
     ( SELECT make
            , MAX(total) total 
         FROM 
            ( SELECT make
                   , model
                   , COUNT(*) total 
                FROM my_table 
               GROUP 
                  BY make
                 , model
          ) x 
       GROUP 
            BY make
     ) b
    ON b.make = a.make 
   AND b.total = a.total;

This one did the trick. You are a life saver Strawberry thank you! I was no where close to getting this one lol. — VinceLovesChickenAndBeer, Nov 24 '17 at 02:56

MySQL display a count of rows for 2 Group Bys

3 Answers3