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

Question

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.

@a_horse_with_no_name MySql – user3602022 Jul 19 '16 at 05:55 — user3602022, Jul 19 '16 at 05:55
Add some sample data and your expected result. – Blank Jul 19 '16 at 06:03 — Blank, Jul 19 '16 at 06:03

score 0 · Accepted Answer · answered Jul 19 '16 at 06:34

0

Try this:

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

SQLFiddle Demo

answered Jul 19 '16 at 06:34

Blank

12,308
1
14
32

areke · Answer 2 · 2016-07-19T06:34:27.130

-1

If the channel_accessed column doesn't matter then you could try

SELECT CountryCode, MAX(user_count)
FROM (SELECT CountryCode,
             COUNT(DISTINCT(user_id)) as user_count
    FROM table_name
    GROUP BY CountryCode)

edited Jul 19 '16 at 06:34

answered Jul 19 '16 at 06:04

areke

126
1
8

You can use an aggregate function on an aggregate function! – sagi Jul 19 '16 at 06:25

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

2 Answers2