MySQL: count unique strings?

Forum Moderators: open

Message Too Old, No Replies

MySQL: count unique strings?

CodilX

8:19 pm on Aug 14, 2008 (gmt 0)

Hi there,

I can't get this bloody thing to work..

This is the database I have:

id ¦ token ¦ value
------------------
1 ¦ 11111 ¦ aaaaa
2 ¦ 11111 ¦ aaaaa
3 ¦ 11111 ¦ aaaaa
4 ¦ 11111 ¦ bbbbb
5 ¦ 11111 ¦ bbbbb
6 ¦ 22222 ¦ ccccc
7 ¦ 22222 ¦ ccccc

My goal is to count the unique values with each token. Like in the example above, there are 3 "aaaaa" values, but I want MySQl to count them as one. The output I'm looking is:

token: 11111, values: 2
token: 22222, values: 1

What I managed to do is just count all the values and group them with the tokens:

SELECT token, count(value) as result FROM database GROUP BY token

But the problem is that this way MySQL counts all of the values, but I just want unique values.

LifeinAsia

8:59 pm on Aug 14, 2008 (gmt 0)

You may need to tweak it a bit for MySQL, but this should get you in the right direction:
SELECT a.token, COUNT(*)
FROM (SELECT DISTINCT token, value
FROM database) a
GROUP BY a.token

CodilX

10:54 pm on Aug 14, 2008 (gmt 0)

Thank you so much! I played around with it and came up with this:

SELECT COUNT(DISTINCT value) AS records FROM database

Thanks again :)

CodilX

10:57 pm on Aug 14, 2008 (gmt 0)

oh wait.. that just show the amount of unique values, but doesn't show which token as how many values :/ bummer.. help please

CodilX

11:06 pm on Aug 14, 2008 (gmt 0)

hrr nevermind :D

SELECT COUNT(DISTINCT value, token) AS values, token FROM database GROUP BY token