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

Question

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 first example?

Answer 1

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

Answer 2

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.