Approfondissement de la fonction COUNT [Analyse de bases de données SQL avec les agrégats (GROUP BY)]

Approfondissement de la fonction COUNT

Syntaxe : COUNT(*)

COUNT(*)renvoie le nombre de lignes de la relation.
COUNT(a)renvoie le nombre de valeurs non nulles de la relation (donc COUNT(*) et COUNT(a) sont équivalents si a est non null)
COUNT (DISTINCT a) renvoie le nombre de valeurs non nulles et distinctes de la relation

Exemple :

CREATE TABLE t (
pk INTEGER PRIMARY KEY,
a VARCHAR(1),
b VARCHAR(1));
INSERT INTO t VALUES (1, 'a', 'x');
INSERT INTO t VALUES (2, 'a', 'y');
INSERT INTO t VALUES (3, 'b', NULL);

CREATE TABLE t (
pk INTEGER PRIMARY KEY,
a VARCHAR(1),
b VARCHAR(1));
INSERT INTO t VALUES (1, 'a', 'x');
INSERT INTO t VALUES (2, 'a', 'y');
INSERT INTO t VALUES (3, 'b', NULL);

table t
#pk	a NOT NULL	b
1	a	x
2	a	y
3	b

SELECT COUNT(*) FROM t : 3
≡ SELECT COUNT(pk) FROM t : 3
≡ SELECT COUNT(a) FROM t : 3
SELECT COUNT (DISTINCT a) FROM t : 2
≡ SELECT COUNT(*) FROM t GROUP BY a : 2

Attention :

Pour des raisons de performance, on préfère l'usage d'un GROUP BY à COUNT (DISTINCT a). On utilise ce dernier que s'il n'existe pas de solution ne mobilisant qu'une seule requête.

Exemple :

SELECT COUNT(*) AS nb_total, COUNT (DISTINCT a) AS nb_a FROM t;

SELECT COUNT(*) AS nb_total, COUNT (DISTINCT a) AS nb_a FROM t;

 nb_total | nb_a 
----------+------
        3 |    2

 nb_total | nb_a 
----------+------
        3 |    2

Attention : COUNT(*) et résultat de requête

Il faut prendre garde à l'usage de COUNT(*) dès que l'on adresse des requêtes portant sur plusieurs tables, en effet cela comptera toutes les lignes produite par le produit cartésien, ce qui n'est pas toujours ce que l'on souhaite.

On préférera compter un attribut non nul.

Exemple : COUNT(*) et jointure externe

Soit la séquence suivante permettant d'instancier t1 et t2, tel que référence t1 référence t2 et qu'il existe un élément de t2 qui n'est jamais référencé.

CREATE TABLE t2 (
pk INTEGER PRIMARY KEY,
a VARCHAR
);
CREATE TABLE t1 (
pk VARCHAR PRIMARY KEY,
fk INTEGER REFERENCES t2(pk)
);
INSERT INTO t2 VALUES (1, 'x');
INSERT INTO t2 VALUES (2, 'x');
INSERT INTO t1 VALUES ('a',1);
INSERT INTO t1 VALUES ('b',1);

CREATE TABLE t2 (
pk INTEGER PRIMARY KEY,
a VARCHAR
);

CREATE TABLE t1 (
pk VARCHAR PRIMARY KEY,
fk INTEGER REFERENCES t2(pk)
);

INSERT INTO t2 VALUES (1, 'x');
INSERT INTO t2 VALUES (2, 'x');

INSERT INTO t1 VALUES ('a',1);
INSERT INTO t1 VALUES ('b',1);

Si l'on utilise un COUNT(*) alors on obtient la valeur 1 pour b au lieu du 0 escompté :

la ligne (b,NULL) est ajoutée au résultat parce que b n'est jamais référencé (principe de la jointure externe)
étant donné que la jointure externe a lieu avant le regroupement et que COUNT(*) compte toutes les lignes

SELECT t2.pk, COUNT(*) 
FROM t2 
LEFT JOIN t1 ON t1.fk=t2.pk
GROUP BY t2.pk;

SELECT t2.pk, COUNT(*) 
FROM t2 
LEFT JOIN t1 ON t1.fk=t2.pk
GROUP BY t2.pk;

 pk | count 
----+-------
  1 |     2
  2 |     1

 pk | count 
----+-------
  1 |     2
  2 |     1

Si l'on utilise un COUNT(a) sur la clé de la table t1, celle-ci étant nulle dans le cas des lignes ajoutées par la jointure externe, le fonctionnement est celui attendu.

SELECT t2.pk, COUNT(t1.pk) 
FROM t2 
LEFT JOIN t1 ON t1.fk=t2.pk
GROUP BY t2.pk;

SELECT t2.pk, COUNT(t1.pk) 
FROM t2 
LEFT JOIN t1 ON t1.fk=t2.pk
GROUP BY t2.pk;

 pk | count 
----+-------
  1 |     2
  2 |     0

 pk | count 
----+-------
  1 |     2
  2 |     0

Complément :

https://sql.sh/fonctions/agregation/count