<p>I have the following query:</p>

<pre><code>select column_name, count(column_name)
from table
group by column_name
having count(column_name) &gt; 1;
</code></pre>

<p>What would be the difference if I replaced all calls to <code>count(column_name)</code> to <code>count(*)</code>?</p>

<p>This question was inspired by <a href="https://stackoverflow.com/questions/59232/how-do-i-find-duplicate-values-in-a-table-in-oracle">How do I find duplicate values in a table in Oracle?</a>.</p>

<hr>

<p>To clarify the accepted answer (and maybe my question), replacing <code>count(column_name)</code> with <code>count(*)</code> would return an extra row in the result that contains a <code>null</code> and the count of <code>null</code> values in the column.</p>

[{'answer_id': 59302, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 9, 'selected': True, 'text': '<p><code>count(*)</code> counts NULLs and <code>count(column)</code> does not</p>\n\n<p>[edit] added this code so that people can run it</p>\n\n<pre><code>create table #bla(id int,id2 int)\ninsert #bla values(null,null)\ninsert #bla values(1,null)\ninsert #bla values(null,1)\ninsert #bla values(1,null)\ninsert #bla values(null,1)\ninsert #bla values(1,null)\ninsert #bla values(null,null)\n\nselect count(*),count(id),count(id2)\nfrom #bla\n</code></pre>\n\n<p>results\n7   3   2</p>\n'}, {'answer_id': 59352, 'author': 'Peter C', 'author_id': 1952, 'author_profile': 'https://Stackoverflow.com/users/1952', 'pm_score': 4, 'selected': False, 'text': '<p>The explanation in the <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-ver15" rel="nofollow noreferrer">docs</a>, helps to explain this:</p>\n<blockquote>\n<p>COUNT(*) returns the number of items in a group, including NULL values and duplicates.</p>\n<p>COUNT(expression) evaluates expression for each row in a group and returns the number of nonnull values.</p>\n</blockquote>\n<p>So count(*) includes nulls, the other method doesn\'t.</p>\n'}, {'answer_id': 59369, 'author': 'Brannon', 'author_id': 5745, 'author_profile': 'https://Stackoverflow.com/users/5745', 'pm_score': 5, 'selected': False, 'text': '<p>Another minor difference, between using * and a specific column, is that in the column case you can add the keyword DISTINCT, and restrict the count to distinct values:</p>\n\n<pre><code>select column_a, count(distinct column_b)\nfrom table\ngroup by column_a\nhaving count(distinct column_b) &gt; 1;\n</code></pre>\n'}, {'answer_id': 61089, 'author': 'Alan', 'author_id': 5878, 'author_profile': 'https://Stackoverflow.com/users/5878', 'pm_score': 4, 'selected': False, 'text': '<p>A further and perhaps subtle difference is that in some database implementations the count(*) is computed by looking at the indexes on the table in question rather than the actual data rows.  Since no specific column is specified, there is no need to bother with the actual rows and their values (as there would be if you counted a specific column). Allowing the database to use the index data can be significantly faster than making it count "real" rows.</p>\n'}, {'answer_id': 3039334, 'author': 'Bill the Lizard', 'author_id': 1288, 'author_profile': 'https://Stackoverflow.com/users/1288', 'pm_score': 3, 'selected': False, 'text': '<p>We can use the <a href="https://data.stackexchange.com/" rel="nofollow noreferrer">Stack Exchange Data Explorer</a> to illustrate the difference with a simple query.  The Users table in Stack Overflow\'s database has columns that are often left blank, like the user\'s Website URL.</p>\n\n<pre><code>-- count(column_name) vs. count(*)\n-- Illustrates the difference between counting a column\n-- that can hold null values, a  \'not null\' column, and  count(*)\n\nselect count(WebsiteUrl), count(Id), count(*) from Users\n</code></pre>\n\n<p>If you run the query above in the <a href="https://data.stackexchange.com/stackoverflow/query/new" rel="nofollow noreferrer">Data Explorer</a>, you\'ll see that the count is the same for <code>count(Id)</code> and <code>count(*)</code>because the <code>Id</code> column doesn\'t allow <code>null</code> values.  The <code>WebsiteUrl</code> count is much lower, though, because that column allows <code>null</code>.</p>\n'}, {'answer_id': 4925414, 'author': 'Ali Adravi', 'author_id': 586227, 'author_profile': 'https://Stackoverflow.com/users/586227', 'pm_score': -1, 'selected': False, 'text': '<p>It is best to use</p>\n\n<pre><code>Count(1) in place of column name or * \n</code></pre>\n\n<p>to count the number of rows in a table, it is faster than any format because it never go to check the column name into table exists or not</p>\n'}, {'answer_id': 9318012, 'author': 'G21', 'author_id': 903005, 'author_profile': 'https://Stackoverflow.com/users/903005', 'pm_score': 2, 'selected': False, 'text': "<ul>\n<li>The COUNT(*) sentence indicates SQL Server to return all the rows from a table, including NULLs. </li>\n<li>COUNT(column_name) just retrieves the rows having a non-null value on the rows.</li>\n</ul>\n\n<p>Please see following code for test executions SQL Server 2008:</p>\n\n<pre><code>-- Variable table\nDECLARE @Table TABLE\n(\n      CustomerId int NULL \n    , Name nvarchar(50) NULL\n)\n\n-- Insert some records for tests\nINSERT INTO @Table VALUES( NULL, 'Pedro')\nINSERT INTO @Table VALUES( 1, 'Juan')\nINSERT INTO @Table VALUES( 2, 'Pablo')\nINSERT INTO @Table VALUES( 3, 'Marcelo')\nINSERT INTO @Table VALUES( NULL, 'Leonardo')\nINSERT INTO @Table VALUES( 4, 'Ignacio')\n\n-- Get all the collumns by indicating *\nSELECT  COUNT(*) AS 'AllRowsCount'\nFROM    @Table\n\n-- Get only content columns ( exluce NULLs )\nSELECT  COUNT(CustomerId) AS 'OnlyNotNullCounts'\nFROM    @Table\n</code></pre>\n"}, {'answer_id': 12530297, 'author': 'Hiren gardhariya', 'author_id': 1564949, 'author_profile': 'https://Stackoverflow.com/users/1564949', 'pm_score': -1, 'selected': False, 'text': '<p>There is no difference if one column is fix in your table, if you want to use more than one column than you have to specify that how much columns you required to count......</p>\n\n<p>Thanks,</p>\n'}, {'answer_id': 16378822, 'author': 'Ahmedul Kabir', 'author_id': 1685054, 'author_profile': 'https://Stackoverflow.com/users/1685054', 'pm_score': 2, 'selected': False, 'text': '<p>Basically the <code>COUNT(*)</code> function return all the rows from a table whereas <code>COUNT(COLUMN_NAME)</code> does not; that is it excludes null values which everyone here have also answered here.\nBut the most interesting part is to make queries and database optimized it is better to use <code>COUNT(*)</code> unless doing multiple counts or a complex query rather than <code>COUNT(COLUMN_NAME)</code>. Otherwise, it will really lower your DB performance while dealing with a huge number of data. </p>\n'}, {'answer_id': 36987526, 'author': 'Unna', 'author_id': 6083307, 'author_profile': 'https://Stackoverflow.com/users/6083307', 'pm_score': -1, 'selected': False, 'text': "<p>As mentioned in the previous answers, <code>Count(*)</code> counts even the <code>NULL</code> columns, whereas <code>count(Columnname)</code> counts only if the column has values. </p>\n\n<p>It's always best practice to avoid <code>*</code> (<code>Select *</code>, <code>count *</code>, …) </p>\n"}, {'answer_id': 55327529, 'author': 'Arun Solomon', 'author_id': 10654073, 'author_profile': 'https://Stackoverflow.com/users/10654073', 'pm_score': 2, 'selected': False, 'text': '<p><code>COUNT(*)</code> – Returns the total number of records in a table (Including NULL valued records).</p>\n\n<p><code>COUNT(Column Name)</code>\xa0–\xa0Returns the total number of Non-NULL records. It means that, it ignores counting NULL valued records in that particular column.</p>\n'}, {'answer_id': 68799071, 'author': 'Payel Senapati', 'author_id': 12118888, 'author_profile': 'https://Stackoverflow.com/users/12118888', 'pm_score': 0, 'selected': False, 'text': '<p>Further elaborating upon the answer given by @SQLMeance and @Brannon making use of <code>GROUP BY</code> clause which has been mentioned by OP but not present in answer by @SQLMenace</p>\n<pre><code>CREATE TABLE table1 ( \nid INT \n);\n</code></pre>\n<pre><code>INSERT INTO table1 VALUES \n(1), \n(2), \n(NULL), \n(2), \n(NULL), \n(3), \n(1), \n(4), \n(NULL), \n(2);\n</code></pre>\n<pre><code>SELECT * FROM table1;\n</code></pre>\n<pre><code>+------+\n| id   |\n+------+\n|    1 |\n|    2 |\n| NULL |\n|    2 |\n| NULL |\n|    3 |\n|    1 |\n|    4 |\n| NULL |\n|    2 |\n+------+\n10 rows in set (0.00 sec)\n</code></pre>\n<pre><code>SELECT id, COUNT(*) FROM table1 GROUP BY id;\n</code></pre>\n<pre><code>+------+----------+\n| id   | COUNT(*) |\n+------+----------+\n|    1 |        2 |\n|    2 |        3 |\n| NULL |        3 |\n|    3 |        1 |\n|    4 |        1 |\n+------+----------+\n5 rows in set (0.00 sec)\n</code></pre>\n<p>Here, <code>COUNT(*)</code> counts the number of occurrences of each type of <code>id</code> including <code>NULL</code></p>\n<pre><code>SELECT id, COUNT(id) FROM table1 GROUP BY id;\n</code></pre>\n<pre><code>+------+-----------+\n| id   | COUNT(id) |\n+------+-----------+\n|    1 |         2 |\n|    2 |         3 |\n| NULL |         0 |\n|    3 |         1 |\n|    4 |         1 |\n+------+-----------+\n5 rows in set (0.00 sec)\n</code></pre>\n<p>Here, <code>COUNT(id)</code> counts the number of occurrences of each type of <code>id</code> but does not count the number of occurrences of <code>NULL</code></p>\n<pre><code>SELECT id, COUNT(DISTINCT id) FROM table1 GROUP BY id;\n</code></pre>\n<pre><code>+------+--------------------+\n| id   | COUNT(DISTINCT id) |\n+------+--------------------+\n| NULL |                  0 |\n|    1 |                  1 |\n|    2 |                  1 |\n|    3 |                  1 |\n|    4 |                  1 |\n+------+--------------------+\n5 rows in set (0.00 sec)\n</code></pre>\n<p>Here, <code>COUNT(DISTINCT id)</code> counts the number of occurrences of each type of <code>id</code> only once (does not count duplicates) and also does not count the number of occurrences of <code>NULL</code></p>\n'}]
['https://Stackoverflow.com/questions/59294', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1288/']