CODEWITHSUNDEEP
mysqlsql
Suhaib Ahmed
Suhaib Ahmed

Reputation: 39

MySQL : How to aggregate table column into a list and count?

I have a table in MySql like :

URL       Browser
A         Chrome
B         Chrome 
C         Firefox
A         Chrome
A         Firefox
A         Opera
A         Chrome
B         Chrome
B         Firefox
C         Tor

The URL column has a wide range of data, but the Browser column has a limited set. I want to aggregate on the URL column and get the top counts for each browser in a list in descending order, like :

URL      FrequentlyUsedBrowser 
A        [(Chrome,3),(Firefox,1),(Opera,1)]
B        [(Chrome,2),(Firefox,1)]
C        [(Chrome,1),(Tor,1)]

I have been writing SQL for it to use window partition to get the count as one entry for each browser, but not been able to get it into a list.

Upvotes: 0

Views: 937

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query. This is achievable using group_concat()

select url
    , concat('[', group_concat('(', browser, ',', ct, ')'), ']') as FrequentlyUsedBrowser 
    from 
    (select count(1) ct, url, browser from test
        group by url, browser) t1
group by url

see dbfiddle

Upvotes: 5

Related Questions