CODEWITHSUNDEEP
sqljoincount
ibmkhd
ibmkhd

Reputation: 799

Help to write SQL query to count

I have 2 tables as follows:

tags: id, version, name

tag_links: id, version, tag_id (foreign key to tags.id)

I need to write an SQL statement that returns how many times each tag_id occurs in tag_links table.

For example:

tags:
    id  version  name
    --  -------  ------
     1        1  sport
     2        1  comedy

tag_links:
    id  version  tag_id
    --  -------  ------
     1        1       1
     2        1       1
     3        1       1
     4        1       2
     5        1       2

The result I need is:

tag_id  times_occurred
------  --------------
     1               3
     2               2

I have a little knowledge of SQL and I tried to write it but :(

Thank you.

Upvotes: 2

Views: 221

Answers (4)

Waheed
Waheed

Reputation: 10206

SELECT   tag_id, 
         Count(*) As times_occurred
FROM     tag_links
GROUP BY tag_id

Upvotes: 0

Wael Dalloul
Wael Dalloul

Reputation: 23014

select id,count(*)  from tags inner join tag_links on tags.tag_id = tag_links.tag_id
group by id

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881473

You don't even need to join tables for this one since all the info you want is in the tag_links table.

select tag_id, count(*) as times_occurred
from tag_links
group by tag_id;

If you wanted the tag names, you'd need to join the tables but that doesn't appear to be the case here.

Upvotes: 8

cletus
cletus

Reputation: 625087

SELECT tag_id, COUNT(*) AS times_occurred
FROM tag_links
GROUP BY tag_id

Upvotes: 3

Related Questions