CODEWITHSUNDEEP
sqlsql-servergroup-by
Eric Xi
Eric Xi

Reputation: 1

How to find max of counts of a group where count is calculated using a function in SQL?

I have a table made up of customer_id and keyword_id. There are multiple occurance of different combinations of customer_id and keyword_id, and I want to find the highest occurring keyword_id for each customer_id. How should I do that?

   Customer_ID . Keyword_ID
      1                 a
      1                 a
      1                 a
      1                 b
      1                 b
      2                 c
      2                 c
      2                 c
      2                 d

Expected Result

Customer_ID .  Max_Keyword_ID . Count
1                 a                 3
2                 c                 3

Upvotes: 0

Views: 45

Answers (4)

PSK
PSK

Reputation: 17943

Another way of doing it using ROW_NUMBER() with PARTITION BY Customer_ID column.

You can try like following.

select *
from
(
    select *, row_number() over(partition by Customer_ID order by ct desc) rn
    from
    (
    select Customer_ID , Keyword_ID, count(*) ct
    from YOURTABLE
    GROUP BY Customer_ID , Keyword_ID
    ) t
) t1
where rn=1

Upvotes: 0

Mano
Mano

Reputation: 788

You can try the following query

 select Customer_ID,Keyword_ID,Count(Keyword_ID) as Count from tab group by 
    Customer_ID,Keyword_ID 
    Having Count(Keyword_ID)=( 
    SELECT MAX(mycount) 
    FROM ( 
    SELECT Keyword_ID, COUNT(Keyword_ID) mycount 
    FROM tab 
    GROUP BY Keyword_ID) checkMaxValue)

Click here to view the reference

Upvotes: 0

Avi
Avi

Reputation: 1845

You can make use of count and dense_rank to get your expected output. Get the rank =1 to make sure that you are getting the rows where you have maximum occurrences of a given output. 

with cte as (
  select  1 as customer_id, 'a' as Keyword_ID  union all 
 select  1 as customer_id, 'a' as Keyword_ID union all  
 select  1 as customer_id, 'a' as Keyword_ID union all  
 select  1 as customer_id, 'b' as Keyword_ID union all  
 select  1 as customer_id, 'b' as Keyword_ID union all  
 select  2 as customer_id, 'c' as Keyword_ID union all  
 select  2 as customer_id, 'c' as Keyword_ID union all  
 select  2 as customer_id, 'c' as Keyword_ID union all  
 select  2 as customer_id, 'd' as Keyword_ID) 

 SELECT customer_id, Keyword_ID, [COUNT]  FROM (
 select customer_id, Keyword_ID, count(1) [COUNT],
 dENSE_RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(1) DESC) RANKED  from cte C 
 group by customer_id, Keyword_ID ) Z 
 WHERE Z.RANKED = 1

Output: 

   customer_id  Keyword_ID  COUNT
      1            a         3
      2            c         3

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below - using correlated subquery

with cte as
(
select Customer_ID,Keyword_ID,count(Keyword_ID) as cnt
from tablename
group by Customer_ID,Keyword_ID
)

select * from cte a where cnt in (select max(cnt) from cte b where a.Customer_ID=b.Customer_ID )

Upvotes: 0

Related Questions