MySQL to count total number of specimens

Question

I have to get total number of specimens in the database.

Table1:

There are 5 specimens as shown in the figure: user 1 has 2, user 2 has 2 and user 3 has 1 number of specimens (users are asked to type a word a number of times and each word is called here a specimen,e.g., cat, bird etc).

I tried following query:

Select count(distinct userId) from  Table1 group by userId

It shows user ID wise total specimens. But what I need is total number of specimens inclusive of all users,i.e, 5.

Answer 1

You can use select distinct with multiple columns:

How do I (or can I) SELECT DISTINCT on multiple columns?

Then you can apply count(*) to the result:

select count(*) from (select distinct userId, specimen from Table1);

Note that it works not with MySQL only but with other dialects as well (including SQLite).