user31782 user31782 - 2 months ago 6
MySQL Question

Why does COUNT() show only one row of table?

I have the following table

pet
in the database
menagerie
:

+--------+-------------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------------+---------+------+------------+------------+
| Tommy | Salman Khan | Lebre | NULL | 1999-01-13 | 0000-00-00 |
| Bowser | Diane | dog | m | 1981-08-31 | 1995-07-29 |
+--------+-------------+---------+------+------------+------------+


Now If I run the following query:

select owner, curdate() from pet;


I get the following output:

+-------------+------------+
| owner | curdate() |
+-------------+------------+
| Salman Khan | 2016-09-12 |
| Diane | 2016-09-12 |
+-------------+------------+


The output show all the values of
owner
, and the value returned from
curdate()
in each row.

Now if I run the following query:

select owner, count(*) from pet;


I get the following output:

+-------------+----------+
| owner | count(*) |
+-------------+----------+
| Salman Khan | 2 |
+-------------+----------+


My question is what is the difference between
curdate()
and
count()
function which makes
MySQL
to output the second
owner
Diane in the second example?

Answer

COUNT() is an aggregation function which is usually combined with a GROUP BY clause.

curdate() is a date function which outputs the current date.

Only MySQL (as far as I know of) allows this syntax without using the GROUP BY clause. Since you didn't provide it, COUNT(*) will count the total amount of rows in the table , and the owner column will be selected randomly/optimizer default/by indexes .

This should be your query :

select owner, count(*) 
from pet
group by owner;

Which tells the optimizer to count total rows, for each owner.

When no group by clause mentioned - the aggregation functions are applied on the entire data of the table.

EDIT: A count that will be applied on each row can't be normally done with COUNT() and usually used with an analytic function -> COUNT() OVER(PARTITION...) which unfortunately doesn't exist in MySQL. Your other option is to make a JOIN/CORRELATED QUERY for this additional column.

Another Edit: If you want to total count next to each owner, you can use a sub query:

SELECT owner,
       (SELECT COUNT(*) FROM pet) as cnt
FROM pet