CODEWITHSUNDEEP
mysqlsql
IrfanRaza
IrfanRaza

Reputation: 3058

SQL query to get agreement list

I have some analytical data for different cases. Each case is associated with one or more photos. Each photo is analyzed by two users. The stored data looks like Source data

What I want is to have SQL query to generate agreement result as shown below Agreement result

So, for case 17116 there is agreement on photo 175062 from user id 26 and 27. Similar case is with photo id 176031 from user id 24 and 29.

Can somebody help me out to achieve this.

Thanks for sharing your valuable time.

Here is sample data to test with

Case Id,Photo Id,FeatureCheck,Result,CheckedBy 17116,173442,severity,none,24 17116,173442,severity,low,25 17116,175062,severity,none,26 17116,175062,severity,none,27 17116,175427,severity,medium,24 17116,175427,severity,high,28 17116,175748,severity,low,22 17116,175748,severity,none,30 17116,176031,severity,low,24 17116,176031,severity,low,29 17277,175309,severity,none,24 17277,175309,severity,none,25 17277,175649,severity,none,24 17277,175649,severity,none,25

Upvotes: 1

Views: 312

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37440

You can try below query:

select PhotoId,
       max(FeatureCheck),
       max(Result),
       max(CheckedBy),
       min(CheckedBy)
from MyTable
group by PhotoId
having count(distinct FeatureCheck) = 1
   and count(distinct Result) = 1

Upvotes: 1

dodzb
dodzb

Reputation: 379

SELECT caseid, photo_id , feature_check, agreedupon, 
   group_concat(checkedby SEPARATOR ',')  as listusers
FROM table1
GROUP BY case_id,  photo_id

Asssuming the possibility of more than 2 users checked the data. Then grouping them is more dynamic.

Upvotes: 0

Related Questions