Not sure if this is a programming question.
I have 2 tables with the following content
Code:
$ db2 "select * from C"
FRUIT SN
------- --
apple 4
apple 5
apple 6
banana 22
banana 23
5 records selected
$ db2 "select fruit, min(sn) as min_sn from U group by fruit"
FRUIT MIN_SN
------- ------
apple 7
banana 15
pear 88
3 records selected
Note that 'pear' does not appear in table C.
Table U contains all fruit entries in this universe.
I want to count each type of fruit (apple, banana, pear) in table C that has Serial Number (SN) smaller than the MIN_SN returned from the 2nd query. Is there is efficient way to do it? The tricky part is that the banana and pear count should be 0.
Code:
$ db2 "
select fruit, count(sn) as count
from (
select MU.fruit, C.sn
from C
right join (select fruit, min(sn) as min_sn from U group by fruit) MU
on C.fruit = MU.fruit
where C.sn < MU.min_sn
)
group by fruit"
FRUIT COUNT
------- -----
apple 3
1 records selected
The above query only shows apple having 3 counts, but no banana or pear. So a union is added:
Code:
$ db2 "
select *
from (
select fruit, count(sn) as count, min_sn
from (
select MU.fruit, C.sn, MU.min_sn
from C
right join (select fruit, min(sn) as min_sn from U group by fruit) MU
on C.fruit = MU.fruit
where C.sn < MU.min_sn
)
group by fruit, min_sn
)
union
(
select fruit, 0 as count, min(sn) as min_sn
from U
where not exists (select 1
from C
where fruit = U.fruit)
group by fruit
)
order by fruit"
FRUIT COUNT MIN_SN
------- ----- ------
apple 3 7
banana 0 15
pear 0 88
3 records selected
This is quite clumsy, don't you think?
