MySQL GROUP BY query

Question

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

gkalpak · Accepted Answer · 2013-10-19 06:43:55Z

1

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.

edited Oct 19, 2013 at 6:43

answered Oct 19, 2013 at 5:54

gkalpak

48.2k8 gold badges108 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aditya Over a year ago

SUM doesnot guarantee the individual count.. individual count of each should be >= 17

gkalpak Over a year ago

@Aditya: I updated my answer (and added a link to a short demo).

Aditya Over a year ago

i also got it with a little diff solution posting it , so u can see that also

danielkza · Accepted Answer · 2013-10-19 06:52:57Z

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.

Aditya · Accepted Answer · 2013-10-19 06:55:40Z

0

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

answered Oct 19, 2013 at 6:55

Aditya

1183 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySQL GROUP BY query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related