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

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?

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

Getting Row Counts in MySQL (part 1), But there's a little more to it than that, as the COUNT() function can be utilized to count all rows in a table or only those rows that match a If it did, concurrent transactions might “see” different numbers of rows at the same time. SQL COUNT rows with user defined column heading . To get number of rows in the 'orders' table with the following condition - 1. result have to display with a heading 'Number of Rows', the following SQL statement can be used: SQL Code: SELECT COUNT( *) as "Number of Rows" FROM orders; Output: Number of Rows ----- 36 SQL COUNT( ) with where clause

This looks exactly like the scenario at the bottom of this page: MySQL Documentation: 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.

MySQL COUNT - Counting Rows in a Table, This tutorial shows you how to use the MySQL COUNT function to count the number The COUNT() function allows you to count all rows or only rows that match a The COUNT() function has three forms: COUNT(*) , COUNT( expression) and´┐Ż The above formula checks whether a sales rep name occurs in one region only or in more than one region. It does that by counting the number of occurrence of a name in a region and dividing it by the total number of occurrences of the name. If the value is less than 1, it indicates that the name occurs in two or more than two regions.

Only MySQL will let you make this kind of queries.

You should always specify all the the columns that are not combined with an aggregation function in the GROUP BY clause . If not, the data will be combined into 1 row, with the aggregated columns set correctly, and all the other columns picked randomly or with the indexes.

So you need this :

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

Which will result :

Saknan Khan |  1
Diane       |  1

Is this what you intended to achieve?

Or maybe you tried to do this:

SELECT t.owner,
       COUNT(*) as cnt
FROM pet t
GROUP BY t.owner

Which will result in an additional column with the total count next to each owner.

Overview of the SQL Count Distinct Function, Sometimes, we want to get all rows in a table but eliminate the SQL COUNT Distinct does not eliminate duplicate and NULL values In the data, you can see we have one combination of city and state that is not unique. Anyway, I added a report file to the project, added an ObjectDataSource and connected this to SQLServer 2008, and display the dataset on the report. The report is using ScriptManager, ReportViewer1, and Report1.rdlc file. I preview the dataset and I have multiple row returned. The problem is I only have one row displayed on the report.

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;

SQL: COUNT Function, The SQL COUNT function is used to count the number of rows returned in a SELECT This website would not exist without the advertisements we display and your kind donations. SELECT COUNT(aggregate_expression) FROM tables [WHERE conditions] Example - COUNT Function only includes NOT NULL Values. Finally, instead of using the data table's "range" dimension, you use the one from your new table. In that case, when you check "show items with no data" like below, it will show the empty rows as well. Not sure if this is the cleverest solution, but it will work.

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.

MySQL Tutorial - MySQL By Examples for Beginners, An Example for the Beginners (But NOT for the dummies) A table is made up of columns (or fields) and rows (records). You can issue a " SHOW CREATE DATABASE databaseName " to display the It is used together with GROUP BY aggregate functions (such as COUNT() , AVG() , SUM() ) to produce group summary. Subject: [access-l] Only one record in form but table shows many Keywords: record,form,table,many rows,rows Send reply to: access-l@Groups.ITtoolbox.com > > > I have a form in Access 2007 that only shows one record (row) although > the table it comes from has many rows.

I have a list of table names, how to I write a query to find out the row count of each table. select table_name from all_tables a where owner='XXX' and num_row > 100 or select table_name from all_tables a where owner='XXX' and sample_size > 100 doesn't quite work. Why? Thanks in advance, Edited by: mpt_fz on Jan 12, 2010 12:06 PM

For example, this is how you can quickly hide all detail rows for the East region, including subtotals, and show only the East Total row: Another way to collapse rows in Excel is to select any cell in the group and click the Hide Detail button on the Data tab, in the Outline group:

When a filter is applied to a Pivot Table, you may see rows or columns disappear. This is because pivot tables, by default, display only items that contain data. In the example shown, a filter has been applied to exclude the East region.

  • @KevinEsche Yes it executes. I've copied the commands and tables from cmd.
  • You get 1 line cuz ya screwed up with no group by :p
  • That query is invalid, and should raise an error. (Perhaps newer MySQL versions will?)
  • @jarlh I am using the latest version 5.7.
  • @jarlh Depends on the value of ONLY_FULL_GROUP_BY see dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
  • 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.
  • 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.
  • @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.
  • @user31782 When you are using an aggregation function with no group by - the output will always be 1 row .
  • How does the subquery work as intended without a WHERE? Doesn't look very correlated to me!
  • Actually I am studying from the docs. I didn't read to the bottom before asking the question, though.