SQL COUNT(*) count every table

Question

When I do an SQL query with a COUNT(*) for example :

SELECT COUNT(*) 
FROM TABLE

It returns 44, this is the exact result. However I need to display something next to it for example :

SELECT COUNT(*), Cars 
FROM TABLEA 
INNER JOIN TABLEB 
ON TABLEA.Key = TABLEB.Key 
GROUP BY Cars 

Except the COUNT(*) no longer displays 44 but 3071. I don't understand why it does that to me. Here is the real SQL query, I don't know if I can provide my database on Stack Overflow:

SELECT COUNT(*) as NBR, Formule
FROM elior_commande
INNER JOIN elior_menu ON numMenuCommande = numMenu
INNER JOIN elior_formule ON idFormule = numFormule
GROUP BY Formule;

I've already tried to LEFT JOIN but it display 2997. When I "COUNT(*)" on "elior_commande", it display 44, that what I want.

Answer 1

When we join 2 tables the number of matching rows for any key is the product of the number of rows in each table.
In the following example we have 2 rows in each table so there are 2 * 2 = 4 matches. One solution is to use count distinct.

create table tableA(
key_ int,
cars varchar(10)
);
insert into tableA values
(1,'Ford'),(1,'Ford');

?

?

create table tableB(
key_ int,
cars varchar(10)
);
insert into tableB values
(1,'Ford'),(1,'Ford');

?

?

SELECT COUNT(*) "number", tableA.Cars 
FROM tableA 
INNER JOIN tableB 
ON tableA.Key_ = tableB.Key_ 
GROUP BY Cars 

number | Cars
-----: | :---
     4 | Ford

SELECT COUNT(distinct tableA.cars) "number", tableA.Cars 
FROM tableA 
INNER JOIN tableB 
ON tableA.Key_ = tableB.Key_ 
GROUP BY Cars 

number | Cars
-----: | :---
     1 | Ford

db<>fiddle here