Query to get distinct count of column with MYSQL

Question

I have a table name POEMS with columns poemID and userID. I want to make the poemID distinct and show only the count in the userID column. There are 3 poemID's with 1. I want to make it show poemID 1 and userID would be 2. poemID 2 and userID 3, for the 3 rows and poemID 3 with userID 1 for the 1 row.

|poemID | userID|
   1       1
   1       5
   2       2
   2       5
   2       4
   3       2

I want the above table to look like the table below.

|poemID | userID |
   1       2
   2       3
   3       1

My SQL query im trying is below but its not working. Please help.

SELECT DISTINCT(poemID), COUNT(userID) FROM POEMS GROUP BY poemID;

you don't need DISTINCT(poemID), just poemID should work. — vb_rises
– vb_rises, Commented Jun 16, 2020 at 23:44
DISTINCT isn't a function, it doesn't apply to a single column. It applies to the entire SELECT list. — Barmar
– Barmar, Commented Jun 16, 2020 at 23:49

GMB · Accepted Answer · 2020-06-16 23:46:30Z

1

This looks like a straight aggregation query:

SELECT poemID, COUNT(*) no_users 
FROM POEMS 
GROUP BY poemID;

Or, if the same user may appear multiple times for a given poem and you want to count it only once:

SELECT poemID, COUNT(DISTINCT userID) no_distinct_users 
FROM POEMS 
GROUP BY poemID;

answered Jun 16, 2020 at 23:46

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

abc903 Over a year ago

Wow you are fast. Thank you GMB!

Collectives™ on Stack Overflow

Query to get distinct count of column with MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related