SQL query for getting count and distinct

Question

I have the following table called user_pin_tags with the following data that I need to query and show distinct tags and a count of each:

| user_id | pin_id | tag_id |
+---------+--------+--------+
    1     |   34   |   7
    2     |   34   |   7
    3     |   34   |   15   
    4     |   34   |   16   
    5     |   34   |   16   
    6     |   34   |   16

Right now I am using the following query to get the distinct tag_ids

SELECT DISTINCT tag_id
FROM user_pin_tags
WHERE pin_id =  34

which gives the result of:

| tag_id |
+--------+
    7
    15
    16

I need to modify this query to give the distinct tag_ids as well as the count within that pin_id, so I want to generate this result set:

| tag_id | count |
+--------+-------+
    7    |   2
    15   |   1
    16   |   3

How can I modify this query to achieve this?

Michael Fredrickson · Accepted Answer · 2012-12-10 19:49:24Z

3

Use COUNT(*) and GROUP BY:

SELECT tag_id, COUNT(*) AS total
FROM user_pin_tags
WHERE pin_id =  34
GROUP BY tag_id

answered Dec 10, 2012 at 19:49

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

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

Comments

d-_-b · Accepted Answer · 2012-12-10 19:50:10Z

2

SELECT 
   DISTINCT(tag_id)
   ,count(pin_id)
FROM user_pin_tags
WHERE pin_id =  34
GROUP BY tag_id

Group By should do it ... i think.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

answered Dec 10, 2012 at 19:50

d-_-b

23.2k43 gold badges174 silver badges291 bronze badges

Comments

Jim · Accepted Answer · 2012-12-10 19:50:00Z

1

You can group by the tag_id:

SELECT tag_id, COUNT(pin_id)
FROM user_pin_tags
WHERE pin_id =  34
GROUP BY tag_id

answered Dec 10, 2012 at 19:50

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Collectives™ on Stack Overflow

SQL query for getting count and distinct

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