To get the multiple maximum-count of repeated values in mysql

Question

How can I get the output for maximum count of repeated values from a table, which contains the repeated values corresponding to a column such a way that there are multiple different distinct values having maximum-counts.

Consider r the table data below :

+---------+------------+-------------+--------------+
| Bill_No | Bill_Date  | Customer_ID | Total_Amount | 
+---------+------------+-------------+--------------+
|     101 | 2012-04-10 | C001        |           64 |
|     102 | 2012-04-10 | C002        |            8 |
|     103 | 2012-04-11 | C002        |          140 |
|     104 | 2012-04-13 | C001        |           29 |
|     105 | 2012-04-12 | C003        |          125 |
|     106 | 2012-04-16 | C004        |          258 |
+---------+------------+-------------+--------------+

We see here maximum count(customer_id) is same for C001 and C002. I want to get both values.

The final output should look like follows:

customer_id     |    count(customer_id)  //max value
----------------+-----------------------
C001            |    2                  
C002            |    2                  
----------------+-----------------------

Can you outline what the result should look like? It's not really understandable from your question. Do you want to sum Total_Amount? — Tobi, Mar 20 '14 at 11:00
I have edited the question. If I made myself clear , can you please help me solve it. — Ritesh, Mar 20 '14 at 11:16

Tobi · Accepted Answer · 2014-03-20T11:28:54.547

1

So, with some guessing, would

select
    distinct g.customer_id,
    g.cnt
from
    (
    select
        distinct customer_id,
        count(customer_id) cnt
    from
        table
    group by 
        customer_id
    ) g
inner join
    (
    select
        max(s.cnt) max_cnt
    from
        (
        select
            distinct customer_id,
            count(customer_id) cnt
        from
            table
        group by
            customer_id
        ) s
    ) m
on
    m.max_cnt = g.cnt

do the trick?

edited Mar 20 '14 at 11:28

answered Mar 20 '14 at 11:02

Tobi

31,405
8
58
90

I need `customer_id` and the corresponding maximum count for `Customer_ID` . – Ritesh Mar 20 '14 at 11:07
What does "maximum count" mean? The overall count of the occourances of the distinct customer_id in your table? then my query should work for you, please try. – Tobi Mar 20 '14 at 11:09
Yes , but I only need the count(s) of those customer_id's which has/have occurred maximum number of times. – Ritesh Mar 20 '14 at 11:17
I really don't get what you mean with "maximum occurances". Only the customer_id's with count equals 2 should be shown? – Tobi Mar 20 '14 at 11:19
By saying "maximum count" , I only want the value of 'maximum count' , which we generally get by using `order by count(customer_id) desc limit 1`. But the problem here is there are 2 such values which have max count viz `C001` and `C002`. But , we generally don't know how many such distinct record will have same max values. And this is what made me stuck. – Ritesh Mar 20 '14 at 11:25

Arnout · Answer 2 · 2014-03-21T08:46:57.123

0

Just use multiple steps.

First save the count for every customer_id. Then select the customer_ids which have the MAX count.

CREATE TEMPORARY TABLE `tmp_count` SELECT count(*) as noOf, customer_id FROM `table` GROUP BY customer_id;
SELECT noOf, customer_id FROM `tmp_count` WHERE noOf = (SELECT MAX(noOf) FROM `tmp_count`);

edited Mar 21 '14 at 08:46

answered Mar 21 '14 at 08:40

Arnout

131
4

score 0 · Answer 3 · answered Mar 21 '14 at 09:00

0

Try this,

;With CTE as
(
SELECT Customer_ID, COUNT(Customer_ID) as Counts
, Rank() over (order by COUNT(Customer_ID) desc) as Rnk
  FROM t GROUP BY Customer_ID 
)
select * from Cte
Where Rnk = 1

answered Mar 21 '14 at 09:00

AK47

3,707
3
17
36

score -1 · Answer 4 · answered Mar 20 '14 at 11:34

-1

if i understand correctly following code is solve you problem:

SELECT cid, COUNT(cid)  FROM t GROUP BY cid;

SQLFIDDLE EXAMPLE:

answered Mar 20 '14 at 11:34

jmail

5,944
3
21
35

To get the multiple maximum-count of repeated values in mysql

4 Answers4