Menu
I would like to know what are the differences between using:
SELECT email, COUNT( email ) AS total
FROM `newsletter`
GROUP BY email having total>1
or
SELECT count(*) as total, email
FROM 'newsletter'
GROUP BY email having total > 1
Both give same results but what else is counting count(*) than the emails?
725
The difference is simple: COUNT(*) counts the number of rows produced by the query, whereas COUNT(1) counts the number of 1 values. Note that when you include a literal such as a number or a string in a query, this literal is "appended" or attached to every row that is produced by the FROM clause.
COUNT(*) counts the rows in your table. COUNT(column) counts the entries in a column - ignoring null values.
Both expressions produce the same result. count(<expr>) takes in account all non- null values of <expr> . 2 is a literal, non-null values, so all rows are taken into account, just like count(*) does.
With COUNT(1), there is a misconception that it counts records from the first column. What COUNT(1) really does is that it replaces all the records you get from query result with the value 1 and then counts the rows meaning it even replaces a NULL with 1 meaning it takes NULLs into consideration while counting.
There's at least one difference.
email can contain NULL.For more information, see this article.
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
