user31782 user31782 - 1 year ago 63
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?

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download