Hey,
When i m using count(*) command for counting the number of records its just not working.
Is there any other way for the same?
please help
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Syed Nayab AliPosted Nov 3, 2011, 3:18 PM
You need to know that COUNT() is not a command, it is a predefined function and every function in SQL must return atleast one value, you should also mention tablename in query from where u want to count row.
select count(*) from table_name
select count(1) from table_name
no doubt both query will give you same result but COUNT(1) is faster than COUNT(*). Suppose you have a table which has more than 100 columns and billions of records then you should use COUNT(1) in place of COUNT(*) because COUNT(1) will generate count only from one column. COUNT(*) counts every record so it takes time.
you can also use column name in place of * & 1
select count(column_Name) from table_name
Hope this will make ur concept clear.
Thanks
Priya LingePosted Nov 3, 2011, 2:14 AM
Its working perfectly.
select COUNT(*) from Customer : result will be the number of records(rows)--Answer -9
select COUNT(1) from Customer :result will be same.
Hope this will help you.
Thanks.
Syed Nayab AliPosted Nov 4, 2011, 2:43 PM
Javeed M ShaikhPosted Nov 3, 2011, 3:41 PM
also keep in mind that Count(*) will consider NULL values also in the count and COUNT(1) will not consider NULL values, so if your data contains NULL then you should consider Count(*). Also the "1" in COUNT(1) is not the column position it is an expression. Count(*) will optimize the query while counting the records by considering the Index Column. So that said if you have an Index Column in your table Count(*) and Count(1) should give the same performance.
Alec StewartPosted Nov 3, 2011, 3:30 AM