While practicing SQL queries, a question has arisen regarding how to perform one. I would like to know what is the difference between:
SELECT open_date, COUNT(*) FROM accounts
with this other
SELECT COUNT(open_date) FROM accounts
While practicing SQL queries, a question has arisen regarding how to perform one. I would like to know what is the difference between:
SELECT open_date, COUNT(*) FROM accounts
with this other
SELECT COUNT(open_date) FROM accounts
The difference is that:
► The first consultation:
SELECT open_date, COUNT(*) FROM accounts
It will return a single row with two columns, the first column will have the first value it finds from the field
open_date
, and the second column will tell you the total number of records in the table.As you can understand, asking for an additional column in the
SELECT
like that would not make much sense.However, it can be used
COUNT
with an additional column (or several), when you want to know how many elements are in that column(s), by combining it withGROUP BY
.For example, if you want to know how many animals there are of each species:
Using that procedure, you can get counts on various types of columns (for other examples see the code examples in the link at the bottom of this answer).
► The second query:
SELECT COUNT(open_date) FROM accounts
It will tell you the total number of records in the table, but ignoring the column values if any
NULL
open_date
. In other words, in that case it might not be counting all the rows in the table.► In short:
COUNT(*)
: will count all records in the table. It can be used tooCOUNT(1)
. However, the form that is usually used is the one with*
.COUNT(columna)
: will count all records in the table where the value ofcolumna
is notNULL
. This usage is not safe if you want to know how many records are in the table, regardless of null values.For more details and examples you can see the MySQL documentation .