CODEWITHSUNDEEP
mysql
Alex
Alex

Reputation: 3958

MySQL Select where count

I have the following MySQL query:

SELECT l.user_id, u.ID, u.ULN, count(u.ULN) As ULN_count
FROM table_Learning l
LEFT JOIN table_users u 
ON l.ULN = u.ULN
WHERE l.user_id is null
AND (SELECT COUNT(ULN) 
    FROM Table_users 
    WHERE ULN = u.ULN 
    GROUP BY ULN) < 2
GROUP BY u.ULN

What I would expect from this query is that The value of the field ULN_Count cannot be greater than 1.

However, the query does not achieve this as the filed has values greater than 1.

What am I doing wrong?

Upvotes: 0

Views: 80

Answers (2)

The Reason
The Reason

Reputation: 7973

You have to move this subquery to having state like this

SELECT 
     l.user_id, u.ID, u.ULN, count(u.ULN) As ULN_count
FROM 
    Table_Learning l
    LEFT JOIN table_users u ON l.ULN = u.ULN
WHERE 
     l.user_id is null
GROUP  BY u.ULN
HAVING  count(u.ULN) < 2

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270609

Your subselect in the WHERE clause is making comparisons on the aggregate COUNT(u.ULN) value, and therefore really belongs in a HAVING clause. It should be significantly more performant there, because the subselect would be evaluated for every row returned by the outer query while the HAVING filter will only be applied once for the entire aggregate query.

SELECT
  l.user_id,
  u.ID, u.ULN,
  count(u.ULN) As ULN_count
FROM
  table_Learning l
  LEFT JOIN table_users u 
    ON l.ULN = u.ULN
WHERE l.user_id is null
GROUP BY u.ULN
-- The aggregate can be compared & filtered in HAVING
-- which is applied just once for the whole query
-- and you can use the alias for comparison
HAVING ULN_count < 2

Without seeing your sample data, it is possible that MySQL's default GROUP BY behavior may cause you some grief. All columns in the SELECT which are not part of an aggregate like COUNT(),SUM(),MAX() should also be included in the GROUP BY, in particular if you are joining multiple tables. In something other than a one-to-one relationship, MySQL may return indeterminate results for the ungrouped columns (l.user_id in this context).

Upvotes: 1

Related Questions