How to combine the related version in group by

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 query 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?

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

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;