user3602022 user3602022 - 4 months ago 12
SQL Question

SQL- Top count(column1) w.r.t Distinct column2

I have a table with three columns,

| User_id (INT) | CountryCode (VARCHAR) | channel_accessed (VARCHAR) |


There is no primary key over here, so repetition is possible for all columns.

I want to write a SQL query that returns Top countries name & there corresponding count w.r.t unique User_id

Tried following this Using group by on multiple columns but this has not helped me much.

sample data :

| User_id (INT) | CountryCode (VARCHAR) | channel_accessed (VARCHAR) |
1 US ARY
2 CA ARY
3 CA MTV
2 CA HUMTV
4 US Tensports
5 US Star Sports
2 CA PTV
2 CA QTV
2 CA NATGEO


Expected Result : US, because it has more unique users.

Answer

Try this:

select CountryCode
from yourtable
group by CountryCode
order by count(distinct User_id) desc
limit 1

SQLFiddle Demo