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

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 petgroup 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 cntFROM pet

This looks exactly like the scenario at the bottom of this page: MySQL Documentation: 4.3.4.8 Counting Rows.

If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is indeterminate. The server is free to select the value from any row:

mysql> SET sql_mode = '';Query OK, 0 rows affected (0.00 sec)mysql> SELECT owner, COUNT(*) FROM pet;+--------+----------+| owner  | COUNT(*) |+--------+----------+| Harold |        8 |+--------+----------+1 row in set (0.00 sec)

I guess in this case only_full_group_by is not set.

Most DBMS systems won't allow a aggregate function like count() with additional columns without a group by; for a reason. The DBMS does not know which columns to group :-).

The solution is to group your query by the owner column, like this:

SELECT owner, count(*) FROM pet GROUP BY owner;