how to combine the related version in group by

Question

select count(a),b,c
from APPLE
  join MANGO  on (APPLE.link=MANGO.link)
  join ORANGE  on (APPLE.link=ORANGE.link)
 where id='camel' 
group by b,c;

the column b gives values like

1.0
1.0,R
1.0,B
2.0
2.0,B
2.0,R
3.0,C
3.0,R

is there a way to modify the above quer so that all 1.0 and 1.0,R and 1.0,B are merged as 1.0 and 2.0,2.0,B are merged as 2.0 and same way for 3.0 and 4.0

Answer 1

SELECT  COUNT(a), REGEXP_REPLACE(b, '([0-9]*\.[0-9]*)(.*)', '\1') b, c
FROM    apple
JOIN    mango
ON      mango.link = apple.link
JOIN    orange
ON      orange.link = apple.link
WHERE   id = 'camel'
GROUP BY
        REGEXP_REPLACE(b, '([0-9]*\.[0-9]*)(.*)', '\1'), c

Answer 2

Hi randeepsp,

you can use substr if you don't have access to regexp_replace (10g+). If you only have to aggregate from 1.0 to 4.0:

SELECT COUNT(a), substr(b, 1, 3), c
  FROM APPLE
  JOIN MANGO ON (APPLE.link = MANGO.link)
  JOIN ORANGE ON (APPLE.link = ORANGE.link)
 WHERE id = 'camel'
 GROUP BY substr(b, 1, 3), c;

If you anticipate you may reach 10.0 you can use instr to get the number of characters:

SELECT COUNT(a),
       CASE
          WHEN instr(b, ',') > 0 THEN
           substr(b, 1, instr(b, ',') - 1)
          ELSE
           b
       END, c
  FROM APPLE
  JOIN MANGO ON (APPLE.link = MANGO.link)
  JOIN ORANGE ON (APPLE.link = ORANGE.link)
 WHERE id = 'camel'
 GROUP BY CASE
             WHEN instr(b, ',') > 0 THEN
              substr(b, 1, instr(b, ',') - 1)
             ELSE
              b
          END, c;