user31782 user31782 - 9 months ago 38
MySQL Question

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

I have the following table

in the database

| 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
, and the value returned from
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
function which makes
to output the second
Diane in the second example?


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