Can anyone solve this mysql statement WHERE COUNT problem?

Question

Im joining two tables together, and i want to list only those rows, which groupped rows count not matching table A's pieces value.

table A
id     name    pieces
1      name_1    6
2      name_2    2

table B
a_id
1
1
2
2

So what i want is to fetch all the a.name where a.piece<>the groupped rows count.
something like this:

SELECT a.name
FROM   a
       INNER JOIN b
               ON a.id = b.a_id
WHERE  a.pieces <> Count(*)
GROUP  BY id

this results an invalid use of group function
also tried this:

SELECT a.name,
       Count(*) AS count
FROM   a
       INNER JOIN b
               ON a.id = b.a_id
WHERE  a.pieces <> count
GROUP  BY id

error: unknown column 'count'

In this case, as result, i only want to receive 1 row like this:

name     count
name_1     2

Because name_1 has 2 grouped rows from table.b and it is not matching a.piece which is 6.

Instead of !== i wanted to write < > but stackoverflow wasnt showing that char combination — Ninet9, Nov 22 '18 at 18:28

score 1 · Accepted Answer · answered Nov 22 '18 at 18:30

1

You can use MAX() on pieces, since it is a scalar value only, it will not make a difference. This will also make the query compliant to only_full_group_by mode

SELECT a.id, a.name, COUNT(*) AS count
FROM   a
       INNER JOIN b
               ON a.id = b.a_id
GROUP  BY a.id, a.name 
HAVING MAX(a.pieces) <> COUNT(*)

answered Nov 22 '18 at 18:30

Madhur Bhaiya

28,155
10
49
57

Can you tell me what is only_full_group_by ? – Ninet9 Nov 22 '18 at 18:35
@Ninet9 check this answer: https://stackoverflow.com/a/41887524/2469308 – Madhur Bhaiya Nov 22 '18 at 18:36
Thanks. I'll be fine with those strict rules. – Ninet9 Nov 22 '18 at 18:39
@Ninet9 yes you should use the strict mode, as it is the right way and ANSI standard compliant – Madhur Bhaiya Nov 22 '18 at 18:40

score 1 · Answer 2 · answered Nov 22 '18 at 18:32

1

You can aggregate the data from tableb and join it with tablea:

SELECT tablea.name
FROM tablea
INNER JOIN (
    SELECT a_id, COUNT(*) AS c
    FROM tableb
    GROUP BY a_id
) AS groupedb ON tablea.id = tableb.a_id
WHERE tablea.pieces <> groupedb.c

answered Nov 22 '18 at 18:32

Salman A

262,204
82
430
521

Can anyone solve this mysql statement WHERE COUNT problem?

2 Answers2