CODEWITHSUNDEEP
sqlcount
Hursey
Hursey

Reputation: 551

SQL Count clarification

Possibly a little off topic, hoping that you might be able to help me solve a bit of an argument.

Story is, I'm working with an 3rd party application which has it's own in built SQL query engine. The developer for this application is 100% convinced that their SQL implementation adheres to all standards. I believe that it doesn't.

Consider the following query:

Select Count(*) 
From TableName 
Where id = 1000

If there is a record with an id of 1000, I get 1 record in my result set.

However, if there is no record with an id of 1000, I get 0 records in my result set. I believe I should get 1 record with a 0 value but the 3rd party developer says that that is correct.

I have pointed him to this (which he chooses to disregard) https://www.w3resource.com/sql/aggregate-functions/count-function.php

To be honest, I don't know for sure exactly how "official" it is.

Am I right or have I got this wrong?

And of course I realise, the 3rd party developer is quite within his rights to ignore this anyway

Upvotes: 0

Views: 51

Answers (1)

Dai
Dai

Reputation: 155290

When evaluating a claim of being correct or standards-compliant we must go to the suitable authority, in this case the SQL-92 specification (which is the simplest and most widely implemented SQL specification and because later specifications do not abrogate SQL-92). Avoid websites that present a watered-down, beginner-friendly, or "real-world" version of a specification when looking for an authoritative source (so your 3rd-party developer is being cautious by ignoring w3resource.com or w3schools.com, for example).

The SQL-92 specification defines COUNT(*) under section 6.5. 6.5 <set function specification> (today these are often instead referred to as "group aggregation functions"). It defines AVG, MAX, MIN, SUM, and COUNT. We are concerned with COUNT(*), which it defines like so (emphasis mine):

The argument of COUNT(*) and the argument source of a <general set function> is a table or a group of a grouped table as specified in Subclause 7.8, "", and Subclause 7.9, "<query specification>".

Let T be the argument or argument source of a <set function specification>.

If COUNT(*) is specified, then the result is the cardinality of T.

In your case:

  • The "the argument source" is the FROM tableName WHERE id = 1000 part of your query, this is T.
  • As your T returns zero results then the cardinality of that result is zero 0.
  • So COUNT(*) will return a value of 0 (which is not NULL either, i.e. COUNT(*) never evaluates to NULL btw) so you are correct: you must have exactly 1 result row and not zero rows. So he is wrong.

EDIT: (from Gordon)

I don't usually do this, but the simple way to express this is that an aggregation query with no group by always returns one. That is what the standard specifies.

Upvotes: 4

Related Questions