Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Derek.

Asked: February 27, 2019 - 12:07 am UTC

Last updated: March 14, 2019 - 9:05 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,

I have a table with many records for example as below.

create table test (a varcahr2(20));

insert into test values ('1');
insert into test values ('2');
insert into test values ('3');

insert into test values ('abc-oo cde');
insert into test values ('cde-oo efg');

insert into test values ('abc-mvis cde');
insert into test values ('cde-mvis efg');


The question is - can I use oracle group by in one SQL to count how many records with numeric? How many records with -oo pattern and how many records with -mvis pattern?

Thanks a lot.

Kind Regards,

Derek

and Chris said...

I'm not sure what it is you want to group by, but you can count how many rows have a property by:

- Placing a case expression within count
- Searching for rows that have the pattern you want
- Return a non-null value for rows that have it, null for those that don't

Count ignores nulls, so this returns the rows that have this property.

Which gives something like:

select count (
         case 
           when validate_conversion ( a as number ) = 1 
           then 1 
         end 
       ) numbers,
       count (
         case 
           when instr ( a, '-oo' ) > 1
           then 1 
         end 
       ) oo,
       count (
         case 
           when instr ( a, '-mvis' ) > 1
           then 1 
         end 
       ) mvis
from   test;

NUMBERS   OO   MVIS   
        3    2      2 


Note validate_conversion is a 12.2 feature. If you're on earlier versions you'll need another method to check if the values are numeric.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Derek Xu, February 27, 2019 - 5:30 pm UTC

Hi Chris,

Thanks a lot and will test it

Kind Regards,

Derek
Connor McDonald
February 28, 2019 - 1:54 am UTC

glad we could help

Derek Xu, March 06, 2019 - 7:52 pm UTC

Hi Chris,

I use REGEXP_LIKE(ORIG_SYSTEM_REFERENCE, '^[0-9]+$') for number. It is working, thanks a lot

select count (
case
When REGEXP_LIKE(a, '^[0-9]+$')
then 1
end
) numbers,
count (
case
when instr ( a, '-oo' ) > 1
then 1
end
) oo,
count (
case
when instr ( a, '-mvis' ) > 1
then 1
end
) mvis
from test;

Kind Regards,

Derek
Chris Saxon
March 07, 2019 - 10:46 am UTC

Great to hear.

Help on a sql query.

Rajeshwaran, Jeyabal, March 08, 2019 - 11:53 am UTC

drop table t purge;
create table t (ID INT, val1 int, val2 int, val3 int);
INSERT INTO t(ID,val1,val2,val3) VALUES (1,10,20,30);
INSERT INTO t(ID,val1,val2,val3) VALUES (1,11,21,31);
INSERT INTO t(ID,val1,val2,val3) VALUES (1,12,22,32);
INSERT INTO t(ID,val1,val2,val3) VALUES (2,11,21,31);
INSERT INTO t(ID,val1,val2,val3) VALUES (2,30,40,50);
INSERT INTO t(ID,val1,val2,val3) VALUES (3,30,40,50);
INSERT INTO t(ID,val1,val2,val3) VALUES (3,41,42,52);
INSERT INTO t(ID,val1,val2,val3) VALUES (4,30,40,50);
INSERT INTO t(ID,val1,val2,val3) VALUES (4,41,42,52);
commit;

demo@ORA12C> select * from t;

        ID       VAL1       VAL2       VAL3
---------- ---------- ---------- ----------
         1         10         20         30
         1         11         21         31
         1         12         22         32
         2         11         21         31
         2         30         40         50
         3         30         40         50
         3         41         42         52
         4         30         40         50
         4         41         42         52

9 rows selected.

In the above example, only ID=3 and ID=4 are same. The ID=3 has two rows and ID=4 has also got 2 rows. Other than ID column, all other column values has to match.

Here is one approach i had, any other alternatives to this?
demo@ORA12C> select id
  2  from (
  3  select id,val1_list,val2_list,val3_list,
  4      count(*) over( partition by val1_list,val2_list,val3_list ) cnt
  5  from (
  6  select id, listagg(val1,',') within group(order by val1) val1_list,
  7             listagg(val2,',') within group(order by val1) val2_list,
  8             listagg(val3,',') within group(order by val1) val3_list
  9  from t
 10  group by id
 11       )
 12       )
 13  where cnt > 1
 14  /

        ID
----------
         3
         4

To Rajesh

A reader, March 10, 2019 - 4:02 am UTC

not sure if it efficient or not, but here is one alternative way.

with data as 
(
    select t.*, count(*) over (partition by id) as cnt1 
    from t
)
select x.id  
from data x join data y
on x.id!=y.id and x.val1=y.val1 and x.val2=y.val2 and x.val3=y.val3 and x.cnt1=y.cnt1
group by x.id, y.id
having count(*)=min(x.cnt1)
order by x.id

Chris Saxon
March 13, 2019 - 2:02 pm UTC

You're accessing the table twice. So it's unlikely to be more efficient.

single pass

Rajeshwaran, Jeyabal, March 14, 2019 - 6:44 am UTC

Thanks Chirs.

but do you see a way of doing this(other than the one mentioned above using LISTAGG) with a single pass to the table?
Chris Saxon
March 14, 2019 - 8:58 am UTC

This is unrelated to the original question - submit a new one if you want help!

what the...

Racer I., March 14, 2019 - 7:59 am UTC

Hi,

For the hijacked second part of this thread :
I don't see a more optimal way for the problem than the listagg one. I would just use order by val1,val2,val3 in the group clause to stabilize the order in case val1 is not unique. It actually blows my mind in its simplicity.
I would however like to know what the real-world use-case behind this requirement is. I don't think I have ever seen a similar problem.

Also what happens with the result? Will one copy be deleted as a duplicate? What if any row in one copy would have been updated later (or one copy had a new row added/deleted)? That would break the match if it happens before the clean-up but be impossible afterwards with only one version left.

regards,
Chris Saxon
March 14, 2019 - 9:05 am UTC

A similar problem would be: find rows with duplicate business keys but different surrogate keys.

This looks subtly different though. I'm not clear on what the full requirement is either.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.