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.

juergen d · Accepted Answer · 2013-04-14 10:59:56Z

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;

answered Apr 14, 2013 at 10:59

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Piruz Hashemian · Accepted Answer · 2013-04-14 11:51:32Z

1

Yes and for more details:

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

answered Apr 14, 2013 at 11:51

Piruz Hashemian

1862 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQLite query aggregation

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related