All geek questions in one place

CHOOSE USING COUNT in mysql

I have a very large database with about 120 million records in one table. First, I clear the data in this table before dividing them into several tables (possibly normalizing them). The columns of this table are as follows: "id (primary key), userId, Url, Tag". This is basically a subset of the data set from a delicious website. As I said, each line has an id, userID url and only one tag. So, for example, a bookmark on a delicious website consists of several tags for one URL, which corresponds to several lines of my database. eg:

 "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 a tag is used for a URL. Thank you for your help.

+4
source share
3 answers

This query should work for you:

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

Did not check it for you though.

+4
source

Is this what you are looking for?

 SELECT COUNT(tag) FROM TABLENAME WHERE tag='sometag'
-1
source

I think this is more like a SELECT tag, COUNT(tag) FROM TABLENAME WHERE URL='someurl' GROUP BY tag

-1
source

Source: https://habr.com/ru/post/1308696/

More articles:


All Articles