SELECT USING COUNT in mysql

Question

I have a very large database with about 120 Million records in one table.I have clean up the data in this table first before I divide it into several tables(possibly normalizing it). The columns of this table is as follows: "id(Primary Key), userId, Url, Tag " . This is basically a subset of the dataset from delicious website. As I said, each row has an id, userID a url and only "one" tag. So for example a bookmark in delicious website is composed of several tags for a single url, this corresponds to several lines of my database. for example:

"id"; "user" ;"url" ;"tag" 
"38";"12c2763095ec44e498f870ed67ee948d";"http://forkjavascript.org/";"ajax" 
"39";"12c2763095ec44e498f870ed67ee948d";"http://forkjavascript.org/";"api" 
"40";"12c2763095ec44e498f870ed67ee948d";"http://forkjavascript.org/";"javascript" 
"41";"12c2763095ec44e498f870ed67ee948d";"http://forkjavascript.org/";"library" 
"42";"12c2763095ec44e498f870ed67ee948d";"http://forkjavascript.org/";"rails"

I need a query to count the number of times that a tag is used for a url. Thank you for you help

Eran · Accepted Answer · 2014-01-21 17:52:41Z

4

This query should work for you:

SELECT tag, url, count(tag) FROM table GROUP BY tag, url

Haven't tested it for you though.

edited Jan 21, 2014 at 17:52

Eran

395k57 gold badges726 silver badges793 bronze badges

answered May 3, 2010 at 21:09

Chris Dale

2,2222 gold badges26 silver badges39 bronze badges

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

Comments

ilija veselica · Accepted Answer · 2010-05-03 21:06:04Z

-1

Is this what you are looking for?

SELECT COUNT(tag) FROM TABLENAME
WHERE tag='sometag'

answered May 3, 2010 at 21:06

ilija veselica

9,59239 gold badges97 silver badges153 bronze badges

Comments

JF. · Accepted Answer · 2010-05-03 21:10:10Z

-1

I think it's actually more like SELECT tag, COUNT(tag) FROM TABLENAME WHERE URL='someurl' GROUP BY tag

answered May 3, 2010 at 21:10

JF.

1631 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

SELECT USING COUNT in mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related