How to Calculate Mode in SQL | How to Find Most Frequent Value in a Column

In this video we will learn 2 methods of how to calculate mode in SQL.
First method will be using pure CTE. Second method will be using Rank function.
Mode is nothing but most frequent value in an Array.
create table mode
(
id int
);
insert into mode values (1),(2),(2),(3),(3),(3),(3),(4),(5);
SQL basics | how to find mode | sql interview questions

[@shaktijyoti4553]

Thanks Brother for your precise explanation,though I had the knowledge about joins still I got some useful insights.

[@divyaaggarwal7824]

I am preparing for interviews and I just came across your channel. The videos are really informative. Thankyou so much !! 👍🏻 Keep up the good work, buddy.

[@ankitbansal6]

Thank you 😊

[@kanchankumar3355]

Much helpful! Thanks for putting it up

[@ankitbansal6]

🙏

[@sanjeetsingh1406]

Great solutions in simple way. I would like to share my interview experience.. This was the 1st questions asked in OPTUM(UHG) interview.. both using SQL & Tableau .. @Ankit your videos are really very helpfull in clearing concepts & basics .. keep up the good work :)

[@ankitbansal6]

Thank you 😊

[@mohammadabdullahansari6314]

For which role?

[@anishchhabra6085]

Excellent explanation bro! I think instead of 2 cte's we can use this code with cte as ( select *, rank() over(order by count(id) desc) as rnk from mode group by id ) select id from cte where rnk = 1;

[@ankitbansal6]

Great 👍

[@Msd.....792]

Great explanation sir, I have also solved it but using subquery and rank function without CTE

[@ankitbansal6]

Cool

[@anujgupta8686]

with cte as( select id, row_number() over (partition by id order by id) most_time from mode) select * from cte where most_time = (select max(most_time) from cte)

[@yosupalex8276]

I'm thinking about using row_number() as well because there will only be only one record in output this way

[@akashsaini704]

thank you

[@themightyvk]

Thanks

[@Mysingh9767]

Thanks... Great video

[@ankitbansal6]

🙏

[@neelshah8803]

Can you please make videos on python and ML

[@godslavepreet]

Good Ankit

[@ankitbansal6]

Thank you 😊

[@soumyagobbani5336]

cte as (select id, rank() over (order by count(*) desc) rwn from mode group by id) select * from cte where rwn =1

[@vishalsonawane.8905]

Done

[@vijaypalmanit]

With you sql seems piece of cake

[@ankitbansal6]

It is piece of cake if you understand fundamentals 😊

[@CloudDataEngineer]

;WITH cte AS (SELECT id, RANK() OVER (ORDER BY COUNT(id) DESC) AS RNK FROM dbo.mode GROUP BY id) SELECT id FROM cte WHERE RNK = 1;

[@grim_rreaperr]

SELECT TOP 1 WITH TIES *,COUNT(1) AS frequency FROM mode GROUP BY id ORDER BY COUNT(1) DESC;

[@armanmardhani8963]

select id, count(id) as occurrences from modes group by id having count(id) = (select max(count(id)) from modes group by id) How about this?

[@prabhatgupta6415]

with cte as (select id,count(*)as m from mode1 group by id order by M desc) select id,M from cte where M=(select max(M) from cte)

[@user-no5mt8jw9q]

Here is my Code: select top 1 id, count(*) as Frequency from mode group by id order by count(*) desc

[@GiriPrasath.D]

select top 1 id, count(*) as Frequency from mode group by id order by frequency desc