CODEWITHSUNDEEP
sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

SQL count function workings

When you run a query like select count(*) from myTable where (myVariable =1) you get a number returned that fits that criteria. If you replace the asterisk with any number, you get the same answer. What's going on with SQL here?

Upvotes: 0

Views: 166

Answers (5)

Johnno Nolan
Johnno Nolan

Reputation: 29659

Its equivalent.

You are asking how many rows are in the table the number n will refer to the nth column in the table. Even if you don't have n columns this will work.

But it gets better you don't even need to put in anything relating to the table SELECT ('X') from Table is valid

There is a school of thought that SELECT(1) is better for performance but MSSQL at least the query optimzer looks after this by choosing the correct plan

Upvotes: 1

Kirill Leontev
Kirill Leontev

Reputation: 10941

count(expression) counts rows where expression is not null.

If you try count(1), then you'll get all the rows, as 1 is never null.

If you try count(column_name), then you'll count rows where column_name is not null.

http://docs.oracle.com/cd/B10500_01/server.920/a96540/functions26a.htm#82699

Upvotes: 0

gjvdkamp
gjvdkamp

Reputation: 10516

Little known feature is that you can query select count(distinct someColumn) from SomeTable to get the unique count of a certain column. Then it does matter wich column you pick obviously.

Else the other answers already explain it.

Upvotes: 0

Habib
Habib

Reputation: 223272

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

Source: SQL: COUNT Function

Upvotes: 1

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20755

Count checks for non null values, so you can pass any value that has non null value e.g. *, field name, static value.

COUNT(*) will count every row. COUNT(yourColumn) won't include rows where yourColumn is NULL

Refer many ways to use Count function

Upvotes: 1

Related Questions