What is the difference between COUNT(*) and COUNT(ColName)?
Question
TheDataMonk
Follow Me
About TheDataMonkGrand Master
I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)Follow Me
Related questions
The Given Data set, Table has transitive dependencies. Explain Which Attributes are partially dependent. How will you overcome this? Explain the approach?
Write a query to Plot a Relationship between the highest followers in every country (Country Wise Highest Followers) and show which country has the highest followers and which country has the lowest one. Explain the approach?
Write a Query to Find the account with the highest number of followers in every country. Explain the approach?
Assign Rank To each Category by authentic engagement in each country and return the category with a rank less than 3. Explain the Approach?
Write a Query to check for Missing values in the given table? Explain how will you deal with the missing data for qualitative and quantitative data?
Answers ( 14 )
COUNT(*) : It will return total number of records in table.
COUNT(ColName) : It will return total number of records where value for that ColName is Not-Null.
Eg: Table A
ID, Name, Dept
1,’A’,’D1′
2,’B’,NULL
3,’C’,’D5′
COUNT(*) : 3
COUNT(Dept) : 2
COUNT(ID) : 3
when you use count(*) it will give count for all the rows in a table
when you use count(colname), it will give count of all the rows where colName is not Null
count(*) – Returns the count of the total record of the table
count(column_name) – Returns the total count of non-null values in the column
count(*) will return the count of the total record of the table
count(column_name) will return the total count of non-null values in the column
COUNT(*) -Returns the count of total records present in the table
COUNT(column_name) Returns the total count of non_null values present in the column.
COUNT(*) : gives the total number of record in the table
COUNT(colname) : gives the total count of the particular column which is passed in COUNT function and it only gives count of NON-NULL values.
for example:
table student
columns : stuID, stuNAME, stuSUBJECT
01,CHETAN,’MATHS’
02,’DIKSHA’,’ENGLISH’
03,’SIDD’,’SCIENCE’
04,’RUCHITA’,NULL
COUNT(*) = 4
COUNT(stuSUBJECT) = 3
The count(*) returns all rows whether column contains null value or not while count(columnName) returns the number of rows except null rows.
COUNT(*) counts all rows
COUNT(column) counts non-NULLs only
Another minor difference between using * and a specific column is that in case of column you can add the keyword DISTINCT and restrict the count to distinct values.
Note: COUNT(1) is the same as COUNT(*) because 1 is a non-null expression
In the SQL query, “COUNT” is the most used function. It is used for taking the count of the records.
We can pass various parameters in the count function. It’s output changes as per the parameters passed in it.
COUNT(*) – Returns the total number of records in a table (Including NULL valued records).
COUNT(Column Name) – Returns the total number of Non-NULL records. It means that it ignores counting NULL valued records in that particular column.
COUNT(*) will return total number of records in table.
COUNT(ColName) will return total number of records where value for that ColName is Not-Null.
Table a
ID, work, add
1,’c’,’D′
2,’x’,NULL
3,’z’,’E′
COUNT(*) : 3
COUNT(add) : 2
COUNT(ID) : 3
COUNT(*) – Returns the total number of records in a table (Including NULL values).
COUNT(Column Name) – Returns the number of Non-NULL records of that particular column. It ignores counting NULL valued records in that particular column.
COUNT(*) returns total number of records in a table.
COUNT(ColName) returns total number of records where value of ColName is Not Null.
e.g.
Table Employee
Id, Name, Exp
1,’a’,2
2,’b’,NULL
3,’c’,4
COUNT(*) : 3
COUNT(Id) : 3
COUNT(Exp) : 2
COUNT(*) return the total number of records in a table.
COUNT(col_name) returns the total number of records where value of col_name is NOT NULL.
Count(*) returns total number of records from whole table.
Count(column_name) returns total number of records from that specified column_name within the table.