Reputation: 49
I have a dataset where in I am trying to find the count of values matched in a column and count of values not matched. Also I am trying to find out same and different values . Eg.
ID F1 F2 F3
A1 B1 D1 10
A1 B1 D1 10
A1 B1 D1 10
A1 B1 D1 20
A1 B1 D1 20
A1 B1 D1 20
A1 C1 D2 10
A1 C1 D2 10
A1 C1 D2 10
A1 C1 D2 10
A1 C1 D2 20
A1 C1 D2 20
A1 C1 D2 20
A1 C1 D2 20
A1 C1 D2 30
A1 C1 D2 30
ID is always same. Column F1 and F2 will change values . I need to find out for an ID how many Column F3 values are same and different in each set of F1 and F2. Also what are those values. Expected output is
ID F1 F2 Count F3 Count F3 F3 values F3 values
matched not matched matched not matched
----------------------------------------------------------------
A1 B1 D1 2 1 10,20 30
A1 C1 D2 2 1 10,20 30
sample code I tried is :
select ID, F1, F2, group_concat(F3,'|') from table
group by ID, F1,F2.
After that I left joined with same table to get different values and count but not progressing well. Any help is highly appreciated.
Regards, A
Upvotes: 1
Views: 102
Reputation: 118
A shorter version to your requirements:
select count(distinct data.F3) from data INTO @numOfF3s;
select
data.ID,
data.F1,
data.F2,
count(distinct data.F3) as 'match F3',
@numOfF3s - count(distinct data.F3) as 'no match F3'
from data
group by data.ID, data.F1, data.F2
gives:
+----+----+----+----------+-------------+
| ID | F1 | F2 | match F3 | no match F3 |
+----+----+----+----------+-------------+
| A1 | B1 | D1 | 2 | 1 |
| A1 | C1 | D2 | 3 | 0 |
+----+----+----+----------+-------------+
Upvotes: 0
Reputation: 10163
Wow, solved it. Look a little complicate query:
select
ID, F1, F2,
group_concat(if(all_groups=1,F3, null)) all_matched_F3,
count(if(all_groups=1,F3, null)) all_matched_F3_count,
group_concat(if(all_groups=0,F3, null)) not_all_matched_F3,
count(if(all_groups=0,F3, null)) not_all_matched_F3_count
from (
select
F3,
count(distinct concat(ID, F1, F2)) = (select count(distinct concat(ID, F1, F2)) from data) all_groups
from data
group by F3
) tmp,
(select distinct ID, F1, F2 from data) groups
group by ID, F1, F2
;
The query works. Can be testetd here SQLize.online
All the query optimization welcome in comments
Upvotes: 1