Query use COUNT(*)

Databases
1

Hi all, I need your help.
Why with this query:


SELECT COUNT(*) AS P_V, A.DU
FROM _table2 A
INNER JOIN _table1 CA ON CA.MAT = A.MAT
GROUP BY A.DU
ORDER BY P_V DESC

I have this output?


P_V	DU
36	B
13	D
9	C
4	A

And I don’t have this output?


P_V	DU
6	B
7	D
3	C
4	A

_table1:


ID	mat
1	a006829
2	a006840
3	a006842
4	a006846
5	a006848
6	a006848
7	a006848
8	a006848
9	a006848
10	a006848
11	a006850
12	a006850
13	a006850
14	a015119
15	a015119
16	a015546
17	a015708
18	a015708
19	a015848
20	a015848

_table2:


ID	MAT	DU
1	a006829	A
2	a006840	A
3	a006842	A
4	a006846	A
5	a006848	B
6	a006848	B
7	a006848	B
8	a006848	B
9	a006848	B
10	a006848	B
11	a006850	C
12	a006850	C
13	a006850	C
14	a015119	D
15	a015119	D
16	a015546	D
17	a015708	D
18	a015708	D
19	a015848	D
20	a015848	D

Can someone help me?
Thanks in advance.

2

That would be correct, because you’re going a count of the joined results, not a count on table A.

Take DU C. You’ve got three records on table A with that DU. Each of them have the same mat on them, a006850. So each of the row on table A will match three rows on table be. So 3 * 3 = 9. See the green records below.

You’ve also got the issue with DU B, mat a006848. There are six records on A with the same mat and 6 * 6 = 36.

The bigger issue is there seems to be duplicates on table A you’re not expecting.

3

Dave: thanks for your reply-
Well if I delete the duplicates in the _table1 I have correct output…
But You can handle these duplicate rows in the _table1?

4

It depends on what the query is supposed to do. What do you want to achieve?
Why do you join the two tables?

If you want to count the number of times a DU is present in table2, then you could try:


SELECT
    COUNT(DISTINCT A.ID) AS P_V
  , A.DU
FROM _table2 A
INNER JOIN _table1 CA ON CA.MAT = A.MAT
GROUP BY A.DU
ORDER BY P_V DESC
5

The solution is your solution… thanks Guido!