CODEWITHSUNDEEP
mysqlcount
ninu
ninu

Reputation: 920

How to perform COUNT() or COUNT(*)

I have a list of tags in a database.

Ex:

villan
hero
spiderman
superman
superman

I wanted to obtain a sorted list of the tag names in ascending order and the number of times the unique tag appeared in the database. I wrote this code:

Ex:

 SELECT hashtag.tag_name
      , COUNT( * ) AS number
   FROM hashtag 
  GROUP BY hashtag.tag_name
  ORDER BY hashtag.tag_name ASC

This yields the correct result:

 hero      , 1
 spiderman , 1
 superman  , 2
 villan    , 1

How can I obtain the full COUNT of this entire list. The answer should be 4 in this case because there are naturally 4 rows. I can't seem to get a correct COUNT() without the statement failing.

Thanks so much for the help! :)

Upvotes: 5

Views: 414

Answers (4)

lc.
lc.

Reputation: 116538

To do it exactly as you're describing (to obtain the full count of the resulting list), you'd want to take a count of the results, like:

SELECT COUNT(*) AS uniquetags
FROM (SELECT hashtag.tag_name, COUNT( * ) AS number
      FROM hashtag GROUP BY hashtag.tag_name
      ORDER BY hashtag.tag_name ASC)

Of course the ORDER BY clause is unnecessary and gets swallowed by the outer aggregate COUNT, as does the inner COUNT.

Additionally, as a few people have pointed out, the shortcut to this is a COUNT DISTINCT, as in:

SELECT COUNT(DISTINCT hashtag.tag_name)
FROM hashtag

This may or may not use indexes more efficiently, depending on whether it realizes it doesn't have to count everything or not. Someone with more knowledge, please feel free to comment (or just try a couple EXPLAINs).

Upvotes: 0

M.J.
M.J.

Reputation: 16676

i am not sure about the query in my-sql but this one works fine with oracle.

SELECT hashtag.tag_name, count(*) FROM hashtag GROUP BY cube(hashtag.tag_name)

Upvotes: 0

Alex Martelli
Alex Martelli

Reputation: 882851

Use COUNT DISTINCT(hashtag.tag_name) -- it can't go in the same SELECT you have (except with a UNION of course), but on a SELECT of its own (or an appropriate UNION) it will give the result you want.

Upvotes: 1

zerkms
zerkms

Reputation: 255155

SELECT COUNT(DISTINCT `tag_name`) FROM `hashtag`

Upvotes: 6

Related Questions