MYSQL - Multiple and condition on different values on same column

Question

I have a table name results

+----+------+------+--------+
| ID | USER | EXAM | RESULT |
+----+------+------+--------+
|  1 |   10 | ssc  | 4.2    |
|  2 |   10 | hsc  | 4.5    |
|  3 |   11 | ssc  | 4.0    |
|  4 |   11 | hsc  | 4.4    |
|  5 |   12 | ssc  | 5.0    |
|  6 |   12 | hsc  | 5.0    |
|  7 |   13 | ssc  | 3.0    |
|  8 |   13 | hsc  | 3.0    |
+----+------+------+--------+

My goal is to retrieve distinct users having a particular score. For example ssc result > 4.0 and hsc result > 4.2.

If I put something like this

select distinct user from results where 
( exam = 'ssc' and result > 4.0 ) 
and 
( exam = 'hsc' and result > 4.2 )

It will return empty as exam cannot equal to 'ssc' and 'hsc' at the same time. If I put or condition in the middle like this

select distinct user from results where 
( exam = 'ssc' and result > 4.0 ) 
or
( exam = 'hsc' and result > 4.2 )

It will include user 11 which is unexpected because our target is to filter the users having greater than 4.0 in ssc.

My expected output will be something like this

10
12

Is there any way we can do this?

@ZaynulAbadinTuhin I have added my expected output to the question. — Iftieaq
– Iftieaq, Commented Oct 2, 2018 at 7:29

D-Shih · Accepted Answer · 2018-10-02 07:40:06Z

4

You can try to use condition aggregate function in HAVING and check both conditions are established.

CREATE TABLE T (
   ID INT,
   `USER` INT,
   EXAM VARCHAR(50),
   RESULT FLOAT
);

INSERT INTO T VALUES (1,10,'ssc', 4.2);
INSERT INTO T VALUES (2,10,'hsc', 4.5);
INSERT INTO T VALUES (3,11,'ssc', 4.0);
INSERT INTO T VALUES (4,11,'hsc', 4.4);
INSERT INTO T VALUES (5,12,'ssc', 5.0);
INSERT INTO T VALUES (6,12,'hsc', 5.0);
INSERT INTO T VALUES (7,13,'ssc', 3.0);
INSERT INTO T VALUES (8,13,'hsc', 3.0);

Query 1:

SELECT USER  
FROM T 
GROUP BY USER 
HAVING 
    SUM(exam = 'ssc' and result > 4.0) = 1 
AND 
    SUM(exam = 'hsc' and result > 4.2) = 1

Results:

| USER |
|------|
|   10 |
|   12 |

answered Oct 2, 2018 at 7:40

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-10-02 07:33:30Z

1

one method using join

  select t1.* from 
(
select * from results where EXAM ='ssc' and RESULT>4.0
    ) t1
  join
(
    select * from  results where EXAM ='hsc' and RESULT>4.2
) as t2 on t1.USER=t2.USER

answered Oct 2, 2018 at 7:33

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

1 Comment

Iftieaq Over a year ago

You solution is also works fine however D-Shih's answer is more clean and small. Thank you anyway :D

Fahmi · Accepted Answer · 2018-10-02 07:34:16Z

1

use conditional aggregation for filter out

select user from tablename
group by user
having 
sum(case when exam='ssc' and result>4.0 then 2 when exam='hsc' and result>4.2 then 1 end)=3

answered Oct 2, 2018 at 7:34

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

MYSQL - Multiple and condition on different values on same column

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related