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?

Hurlow

@KevinEsche Yes it executes. I've copied the commands and tables from cmd. – Hurlow

You get 1 line cuz ya screwed up with no group by :p – Sos

That query is invalid, and should raise an error. (Perhaps newer MySQL versions will?) – Outrageous

@Outrageous I am using the latest version 5.7. – Hurlow

@Outrageous Depends on the value of ONLY_FULL_GROUP_BY see dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – Plod

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

Osteology

I don't think group by is necessary becasue SELECT COUNT(*) FROM pet; just counts all the rows from pet. So, like curdate() displays same date twice in two rows; count(*) should also display total_rows twice in two rows in my second example. – Hurlow

Yes, only MySQL, so far as I'm aware, has this "damn the torpedoes, I don't care if what you've asked for is nonsense, if I possibly can, I'm going to wildly guess and return a result set rather than give the user a warning" attitude. – Langston

@Hurlow - the difference is, you've got an additional column you're asking for in the select. MySQL doesn't know whether you intended to group by that column, or wanted an arbitrary value, or you wanted to apply some aggregate to it. – Langston

@Hurlow When you are using an aggregation function with no group by - the output will always be 1 row . – Osteology

How does the subquery work as intended without a WHERE? Doesn't look very correlated to me! – Wet

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.

Radborne

Answer 3

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;

Device

Answer 4

The last query is invalid for Oracle: ORA-00937: not a single-group function. This means you need a GROUP BY clause. You found a loophole in the MySql implementation. Do not rely on such a query in a production system, in a next version of MySql this might not work.

Floris

Answer 5

Count(*) aggregate function it returns only one value and i.e. total number of rows. And curdate() function is just provide the system's current date.

Sha