CODEWITHSUNDEEP
mysql
Pr0no
Pr0no

Reputation: 4109

Count unique strings in table

I have the following table (yes, the id_str are integers formatted as text):

messages
-------------------------
id (INT) id_str (VARCHAR)
-------------------------
1        3245346543343247
2        3245346543343247
3        3245346543343247
4        1214354546578886
5        9979679595696599
6        9979679595696599

The desired output is a count of all unique id_str's:

id_str            cnt
---------------------
3245346543343247  3
1214354546578886  1
9979679595696599  2

I'm trying to accomplish this with the following query:

SELECT DISTINCT(id_str) AS id_str, COUNT(id) FROM messages

But this returns the exact table as is, with the total number of messages as a second column. What am I doing wring?

Upvotes: 1

Views: 850

Answers (4)

Sashi Kant
Sashi Kant

Reputation: 13465

Try this:

SELECT id_str AS id_str, COUNT(id) 
FROM messages group by id_str

Upvotes: 1

spencer7593
spencer7593

Reputation: 108510

This will give you a count of rows for each distinct value of id_str. Note that this will count all of the rows.

SELECT id_str, COUNT(1) AS cnt FROM messages GROUP BY id_str

If you only want to count rows where a given expression is not null (for example, where id is not null) then you can apply the COUNT aggregate to that expression. Only rows that have a non-NULL value for the expression will be included in the count (so that it's possible, under some conditions, to actually return a count of zero):

SELECT id_str, COUNT(id) AS cnt FROM messages GROUP BY id_str

(Of course, if the id column is guaranteed to be NOT NULL, then these two will return the same result.)

NOTE: You don't need a DISTINCT keyword, the GROUP BY clause does what you need.

Your query is equivalent to:

SELECT DISTINCT id_str, COUNT(id) FROM messages

The DISTINCT keyword operates on ALL the expressions in the SELECT list, not just the first one. The parenthesis around the first expression doesn't change the behavior. DISTINCT is a reserved word, it's not a function call. The AS id_str alias in your query is being assigned to just the the id_str column, which just happens to be enclosed in the unnecessary (but legal and allowed) parenthesis.

Upvotes: 1

AnAmuser
AnAmuser

Reputation: 1895

You need a group by.

SELECT DISTINCT(id_str) AS id_str, COUNT(id) FROM messages GROUP BY id_str

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65587

You need to use GROUP BY:

SELECT id_str, COUNT(id) as cnt
FROM messages
GROUP BY id_str

Upvotes: 7

Related Questions