SQLite query aggregation

Question

I have two columns of data in a SQLite table: users and URL. I'm trying to construct a query that returns the users with a particular URL. I'm trying the following:

SELECT url, COUNT(url) as RESULT 
FROM bookmarks 
GROUP BY url 
order by RESULT desc
limit 10;

This query is close, but I only get back one user—not an aggregation of all the users for a given bookmark URL.

Answer 1

Yes and for more details:

SELECT ... GROUP_CONCAT(users ORDER BY users ASC SEPARATOR ', ') AS user_list
FROM ...

Answer 2

Use group_concat() to get a list of users for every url

SELECT url, 
       COUNT(users) as RESULT, 
       group_concat(users) as user_list
FROM bookmarks 
GROUP BY url 
order by RESULT desc 
limit 10;