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?

@KevinEsche Yes it executes. I've copied the commands and tables from `cmd`. — user31782, Sep 12 '16 at 07:04
That query is invalid, and should raise an error. (Perhaps newer MySQL versions will?) — jarlh, Sep 12 '16 at 07:19
@jarlh Depends on the value of [`ONLY_FULL_GROUP_BY `](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) see https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html — Ferrybig, Sep 12 '16 at 08:54

sagi · Accepted Answer · 2016-09-12T10:01:03.083

61

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

edited Sep 12 '16 at 10:01

answered Sep 12 '16 at 07:08

sagi

40,026
6
59
84

1

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. – user31782 Sep 12 '16 at 07:12
24

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. – Damien_The_Unbeliever Sep 12 '16 at 07:12
@user31782 - 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. – Damien_The_Unbeliever Sep 12 '16 at 07:13
9

@user31782 When you are using an aggregation function with no group by - the output will **always** be 1 row . – sagi Sep 12 '16 at 07:21
How does the subquery work as intended without a `WHERE`? Doesn't look very correlated to me! – Lightness Races in Orbit Sep 12 '16 at 09:59

score 7 · Answer 2 · edited Sep 12 '16 at 23:20

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.

score 2 · Answer 3 · answered Sep 12 '16 at 07:12

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;

score 2 · Answer 4 · answered Sep 13 '16 at 08:44

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.

score 1 · Answer 5 · answered Sep 12 '16 at 07:41

1

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.

answered Sep 12 '16 at 07:41

soniabhishek36

59
5

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

5 Answers5

Linked