Group up over consecutive records, save for the last character

Question

Suppose I have a table, which looks like the following:

InventoryVAS     colVAS     IP

fvas1               5       172.21.4.97
fvas2               6       172.21.4.97
abcd                4       10.4.6.89
ef1                 7       169.21.5.67
ef2                 8       169.21.5.67

Whenever there are 2 same consecutive IPs, the InventoryVAS columns will always have same value except for last character.

e.g. fvas in fvas1 and fvas2 for 172.21.4.97

I need my final table to be like this:

InventoryVAS     colVAS     IP

fvas                5/6     172.21.4.97
abcd                4       10.4.6.89
ef                  7/8     169.21.5.67

In real scenario, my table has 100s of records which have same IPs and InventoryVAS value same for that IP except the last character. I need to exclude this last character so as to make it a single row with middle column (colVAS) value = (value in row1)/(value in row2).

I've tried Gordon's answer and got the following:

create table VAS(
InventoryVAS varchar2(40),
colVAS       number,
IP           varchar2(40)
)

insert into VASInventory values ('fvas1',5,172.21.4.97);
insert into VASInventory values ('fvas2',6,172.21.4.97);
insert into VASInventory values ('abcd',4,10.4.6.89);
insert into VASInventory values ('ef1',7,169.21.5.67);
insert into VASInventory values ('ef2',8,169.21.5.67);
select * from VAS;

select substr(InventoryVas, length(InventoryVas) - 1) as InventoryVas,
       (case when min(colvas) <> max(colvas) then min(colVas)||'/'||max(colVas)
             else min(colvas)
        end) as colVas,
       ip
from VAS
group by substr(InventoryVas, length(InventoryVas) - 1), ip;
---------------------------------------------------------------------------
ERROR :
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 24 Column: 19

create table VAS(
InventoryVAS varchar2(40),
colVAS       number,
IP           varchar2(40)
)
drop table VAS;
insert into VASInventory values ('fvas1',5,172.21.4.97);
insert into VASInventory values ('fvas2',6,172.21.4.97);
insert into VASInventory values ('abcd',4,10.4.6.89);
insert into VASInventory values ('ef1',7,169.21.5.67);
insert into VASInventory values ('ef2',8,169.21.5.67);
select * from VAS;

select substr(InventoryVas, length(InventoryVas) - 1) as InventoryVas,
       (case when min(colvas) <> max(colvas) then min(colVas)||'/'||max(colVas)
             else min(colvas)
        end) as colVas,
       ip
from VAS
group by substr(InventoryVas, length(InventoryVas) - 1), ip;
---------------------------------------------------------------------------
ERROR :
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 24 Column: 19

The reason for the error is that a [CASE statement requires that the datatypes returned are the same](http://stackoverflow.com/questions/13712763/are-a-case-statement-and-a-decode-equivalent); because you're concatenating in your THEN clause you end up with a string whereas `min(colvas)` would be a number. Change it to `to_char(min(colvas))` and it should work. — Ben, Sep 08 '13 at 19:17
P.S. How do you define consecutive? i.e. if you had another row `ef3, 10, 169.21.5.67` at the bottom would this be consecutive? — Ben, Sep 08 '13 at 19:19

score 0 · Answer 1 · answered Sep 07 '13 at 21:42

Does this do what you need:

select substr(InventoryVas, len(InventoryVas) - 1) as InventoryVas,
       (case when min(colvas) <> max(colvas) then min(colVas)||'/'||max(colVas)
             else min(colvas)
        end) as colVas,
       ip
from t
group by substr(InventoryVas, len(InventoryVas) - 1), ip;

This makes the middle column have the minimum of colvas and the maximum, separated by a '/'.

score 0 · Answer 2 · answered Sep 09 '13 at 07:33

This will give you the correct INVENTORYVAS 'abcd' if there's no duplicate IP. The top aggregate function can be MIN/MAX. Its only there to make the grouping work.

SELECT MIN ((CASE WHEN cnt = 1 THEN v2.InventoryVAS ELSE v1.InventoryVAS END)) AS InventoryVAS,
     v1.colvas,
     v1.ip
FROM (SELECT SUBSTR (InventoryVas, 0, LENGTH (InventoryVas) - 1) AS InventoryVas,
                  (CASE
                      WHEN MIN (colvas) <> MAX (colvas)
                      THEN
                         MIN (colVas) || '/' || MAX (colVas)
                      ELSE
                         TO_CHAR (MIN (colvas))
                   END) AS colVas,
                  IP,
                  COUNT (*) AS cnt
             FROM VAS
         GROUP BY SUBSTR (InventoryVas, 0, LENGTH (InventoryVas) - 1), ip) v1
     JOIN VAS v2 ON v2.IP = v1.IP
GROUP BY v1.ip, v1.colvas

Maybe it can be refined further!

Group up over consecutive records, save for the last character

2 Answers2