SQL count function workings

Question

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?

You're getting the same count and that surprises you - so what did **you** expect instead?? — marc_s, May 10 '12 at 12:35
`count()` should not take a number as parameter, but names of fields (or `*`). — sp00m, May 10 '12 at 12:37
dup http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count — ilanco, May 10 '12 at 12:37
i expected the query to fail when you put something in there like count(343), not ignore it. — wootscootinboogie, May 10 '12 at 12:37
Duplicates http://stackoverflow.com/questions/1221559/count-vs-count1 — ericosg, May 10 '12 at 12:44

Johnno Nolan · Accepted Answer · 2012-05-10T12:50:10.703

1

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

edited May 10 '12 at 12:50

answered May 10 '12 at 12:38

Johnno Nolan

29,228
19
111
160

Thanks for answering the question instead of blasting it. Will this take NULLS into account? Say if you do select count(5) and the fifth column is full of NULLS will it still return the number of rows in the table? – wootscootinboogie May 10 '12 at 12:39
It doesn't. select count(5) from dual would yield an error then. – Kirill Leontev May 10 '12 at 12:41
The query compiler ignores it. – Johnno Nolan May 10 '12 at 12:43

score 1 · Answer 2 · answered May 10 '12 at 12:39

1

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

answered May 10 '12 at 12:39

Romil Kumar Jain

20,239
9
63
92

score 1 · Answer 3 · answered May 10 '12 at 12:40

1

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

answered May 10 '12 at 12:40

Habib

219,104
29
407
436

I'm pretty sure that most databases do *not* return the data fields for a count(*) versus a count(1). This is a very basic optimization for the database optimizer. – Gordon Linoff May 10 '12 at 13:18

score 0 · Answer 4 · answered May 10 '12 at 12:41

0

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.

answered May 10 '12 at 12:41

gjvdkamp

9,929
3
38
46

score 0 · Answer 5 · answered May 10 '12 at 12:43

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

SQL count function workings

5 Answers5