0
select patientID, count(1)    
from dbo (nolock)    
where admissiontime between '2020-01-31' and '2020-02-01'

patientID in (0, 1)

/*
0 means arthritis

1 means asthma
*/

So above query is used to find how many times there was a request from hospital for both asthma and arthritis related case.

It is put in a group by the patientID, but does that count(1) mean it counts everything patientID?

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • 2
    please, post actual query, this one make no sense (group by, dbo as a table?).... – MLeblanc Sep 11 '20 at 23:00
  • 2
    [Stop splattering nolock everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). You shouldn't need to be told this multiple times. – SMor Sep 12 '20 at 00:15

1 Answers1

1

Basically, count(1) produces just the same result as count(*): that is, it counts the number of records in the group defined by the group by clause.

Why? count(<expr>) counts every non-null value of <expr>. Here it is given a constant value, 1, that is never null - so it counts all rows.

I wouldn't recommend count(1); checking for nullity of each and every row requires more work for the database that just counting all rows. Some databases might optimize count(1) as count(*) under the hood (as it is obvious that this fixed expression will never be null), but why bother, when you can optimize yourself already? Just use count(*) consistently.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    both count(1) and count(*) are the same. Count(1) was to make sure there's no NULL value, but now count(*) does the same thing https://stackoverflow.com/questions/1221559/count-vs-count1-sql-server https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-ver15 – MLeblanc Sep 11 '20 at 23:13
  • 1
    @MLeblanc by "now" do you mean "since the 1990s"? :-) – Aaron Bertrand Sep 12 '20 at 01:47
  • exactly, the semantic meaning goes away using count(1) which says-> count the number of (1) in a table, why stop at 1?, you could use count(42),count(3),count(4) as well. However count(*) -> count all the records in the table. * meaning the wildcard for "all" – George Joseph Sep 12 '20 at 03:03
  • @AaronBertrand i was taught at university (in the early 2000) that count(*) would fetch all rows and would count only rows that have at least one non-null value, and since your table should have PK, that made no sense to do a count(*). But I was curious and take a look at standard sql-92,sql-89 and sql-86 and they all stated that "If COUNT(*) is specified, then the result is the cardinality of R. ". So, i guess "now" mean "since the beginning" :-) – MLeblanc Sep 12 '20 at 14:48