How to compare values with the same ID but different other attribute?

Question

+------+------+------+
| ID   |letter|number|
+------+------+------+
|1     |A     | 23   |
+------+------+------+
|1     |B     | 45   |
+------+------+------+
|2     |A     | 37   |
+------+------+------+
|2     |B     | 24   |
+------+------+------+
|2     |C     | 26   |
+------+------+------+
|3     |A     | 47   |
+------+------+------+
|3     |C     | 35   |
+------+------+------+

Noob here

I want to find all instances where the number for a given ID is smaller when the letter is A than it is when the letter is B. For example, when ID is 1, number for A is 23 and number for B is 45. Since 23 < 45, it fits the criteria, and I would like to return the ID 1 as well as the sum of the two numbers (23 + 45). ID = 2 does not work because 37 is not less than 24. ID = 3 also does not work because it doesn't have an entry with letter B.

which mysql version are you using? – nbk Aug 31 '20 at 22:23 — nbk, Aug 31 '20 at 22:23

score 0 · Accepted Answer · answered Aug 31 '20 at 22:25

Use conditional aggregation in a having clause:

select id, sum(number) number_total
from mytable
where letter in ('A', 'B')
group by id
having min(case when letter = 'A' then number end) < min(case when letter = 'B' then number end)

This assumes that (id, letter) tuples are unique in the table. Otherwise you maybe want SUM() instead of MIN().

If you wanted to display the values as well, then MySQL lets you use aliases in the having clause:

select 
    id, 
    sum(number) number_total,
    min(case when letter = 'A' then number end) number_a,
    min(case when letter = 'B' then number end) number_b
from mytable
where letter in ('A', 'B')
group by id
having number_a < number_b

How to compare values with the same ID but different other attribute?

1 Answers1