CODEWITHSUNDEEP
mysql
Aditya
Aditya

Reputation: 118

MySQL GROUP BY query

table-- t_id_with_data_and_count

column - f_id , f-data , f_count

its a many to many map table (any id can have any number of records(with f_id and f_data set being unique in nature))

SELECT * FROM t_id_with_data_and_count where f-data IN ('PHP','JAVA') GROUP BY 1 HAVING COUNT(*)= 2 AND f_count >=17

Expected result : i am expecting it should return me all ids who are having php and java and count of php , java >= 17

results getting now: getting all the ids with both php and java , but count it is checking for java only, if the count of java is >= 17 it throws me the result.

but i am looking for it should also check for count of php

Upvotes: 0

Views: 49

Answers (3)

Aditya
Aditya

Reputation: 118

SELECT f_id FROM t_id_with_data_and_count WHERE f_data IN ('PHP','JAVA') AND f_count >=17 GROUP BY f_id HAVING COUNT( DISTINCT f_data ) = 2

Upvotes: 0

danielkza
danielkza

Reputation: 2889

Conditions on the HAVING clause will be evaluated against the values computed for the GROUPed row, not the original rows. Each non-grouped column can obviously only contain the value from one of the group members, so you cannot guarantee an universal condition through it. Therefore you need to move your comparison to the WHERE clause. But you need to specify a bit more what you want, since there are two possible interpretations.

If the f_id and f_data combination is UNIQUE there can only be at most two rows, so simply checking f_count in the WHERE clause is enough:

SELECT *
FROM t_id_with_data_and_count
WHERE f_data IN ('PHP','JAVA')
  AND f_count >= 17
GROUP BY f_id
HAVING COUNT(*);

Otherwise you need to specify whether you need at least two rows with a large enough count, or if you want all of the rows for an id and set of languages to have f_count >= 17 and that there are at least a given number of them.

Upvotes: 0

gkalpak
gkalpak

Reputation: 48211

Since you group by f_id, f_count in the havinf clause has the value of either a 'PHP' or a 'JAVA' row.

What you need to check is that f_count is >= 17 and also that the sum of f_counts is >= (17 + the "arbitrary" f_count).
(In other words: If the f_id has both 'PHP' and 'JAVA' rows and one of the f_counts is >= 17 and the sum of the two f_counts is >= (17 + the one f_count (that we checked is >= 17) then we found a match !)

SELECT *
  FROM t_id_with_data_and_count
  WHERE f_data IN ('PHP', 'JAVA')
  GROUP BY f_id
  HAVING COUNT(f_id) = 2
    AND f_count >= 17
    AND SUM(f_count) >= f_count + 17

See, also, this short demo.

Upvotes: 1

Related Questions