Question: My professor taught me this SQL statement:

```
SELECT COUNT(length) FROM product

```

will return `2` with the following dataset:

```
product
|id | length | code |
|-------------------|
| 1 |    11  | X00  |
| 2 |    11  | C02  |
| 3 |    40  | A31  |

```

She justified it by saying that `COUNT` doesn't count duplicates. I disagreed. After trying a lot of DBMS, I've never found one that has this behaviour. Does such an DBMS exist?

Answer: 
`COUNT` does count duplicates in all DBMS I'm aware of, but.

> 
> Is there any reason for a professor to teach this behaviour
> 
> 
> 

Yes, there is a reason. In the original relational theory (that underlies all modern relational DBMSes) *relation* is a *set* in mathematical sense of this word. That means that no relation can contain duplicates at all, including all transitional relations, not just your “tables”.

Following this principle you may say that `SELECT length FROM product` already contains only two rows, hence corresponding `COUNT` returns `2`, not `3`.

---

For example, in [Rel](https://reldb.org/c/) DBMS, using the relation given in the question and [Tutorial D](https://en.wikipedia.org/wiki/D_(data_language_specification)) syntax:

```
SUMMARIZE product {length} BY {}: {c := COUNT()}

```

gives:

[![Rel result](https://i.stack.imgur.com/CKNbi.png)](https://i.stack.imgur.com/CKNbi.png)
If your professor is talking about SQL, the statement is wrong. `COUNT(x)` will return the number of rows where x `IS NOT NULL` including duplicates. `COUNT(*) or COUNT([constant])` is a special case that will count the rows, even those where every column is `NULL`. However, duplicates are always counted, unless you specify `COUNT(distinct x)`. Example:

```
with t(x,y) as ( values (null,null),(null,1),(1,null),(1,1) )

select count(*) from t
4

select count(1) from t
4

select count(distinct 1) from t
1

select count(x) from t
2

select count(distinct x) from t
1

```

`COUNT(distinct *)` is invalid AFAIK. 

As a side note, NULL introduces some unintuitive behaviour. As an example:

```
SELECT SUM(x) + SUM(y),  SUM(x + y) FROM T
4, 2

```

i.e:

```
SUM(x)+SUM(y) <> SUM(x+y)

```

If he/she is talking about a relational system as described by, for example, the book [Databases, Types, and the Relational Model: The Third Manifesto](https://www.amazon.co.uk/Databases-Types-Relational-Model-C-J/dp/0321399420/sr=1-2/qid=1169757255/ref=sr_1_2/203-3128636-8071105?ie=UTF8&s=books) by C. J. Date and Hugh Darwen - it would be a correct statement.

Say that we have the relation:

```json
STUDENTS = Relation(["StudentId", "Name"]
                    , [{"StudentId":'S1', "Name":'Anne'},
                       {"StudentId":'S2', "Name":'Anne'},
                       {"StudentId":'S3', "Name":'Cindy'},
                     ])

```

```
SELECT COUNT(NAME) FROM STUDENTS

```

corresponds to:

```
COUNT(STUDENTS.project(['Name']))

```

i.e.

```json
COUNT( Relation(["Name"]
               , [{"Name":'Anne'},
                  {"Name":'Cindy'},
                ]) )

```

which would return **2**.