MySQL Select where count

Question

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?

Please show some sample data from the two involved tables. It is possible you are running afoul of MySQL's lenient GROUP BY behavior because you have several columns in SELECT not also included in GROUP BY and not aggregated. That will often cause indeterminate groupings, and results in a compile error in most RDBMS besides MySQL. — Michael Berkowski
– Michael Berkowski, Commented Jun 23, 2015 at 13:21
But looking at the structure of your subselect in WHERE, it seems like that entire thing may be eliminated from WHERE and replaced with HAVING COUNT(u.ULN) < 2 — Michael Berkowski
– Michael Berkowski, Commented Jun 23, 2015 at 13:23
@MichaelBerkowski if you put that as an answer I will mark it as correct — Alex
– Alex, Commented Jun 23, 2015 at 13:25
@MichaelBerkowski yes but you answered it so i am not marking the one below as correct — Alex
– Alex, Commented Jun 23, 2015 at 13:27

Michael Berkowski · Accepted Answer · 2015-06-23 13:35:27Z

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).

Ajay2707 · Accepted Answer · 2015-06-23 13:36:48Z

1

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

edited Jun 23, 2015 at 13:36

Ajay2707

5,8286 gold badges44 silver badges61 bronze badges

answered Jun 23, 2015 at 13:26

The Reason

8,0135 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

MySQL Select where count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related