CODEWITHSUNDEEP
oracle-database
Lucas Rey
Lucas Rey

Reputation: 467

Oracle query to count and group specific field

I have the following example table:

FIELD1     FIELD2      FIELD3
12345      888555      009999
12345      888555      327777
12345      888555      327777
54321      999666      334444
54321      999666      324444
55501      999333      005555
55501      999333      350000
55501      999333      890000
55501      999333      320000

Now, assuming FIELD1 and FIELD2 have always the same value, e.g. if FIELD1 = 12345 then FIELD2 will be always 888555, what I have to do is make a query where I count FIELD1 and FIELD2 occurencies when the first 2 digits of FIELD3 = 32 and when the first 2 digits of FIELD3 IS NOT 32.

In above case I should have as output:

FIELD1  FIELD2    FIELD3    COUNT
12345   888555    32            2
54321   999666    NOT32         1
54321   999666    32            1
55501   999333    32            1
55501   999333    NOT32         3

Could someone help me to achieve that?

EDIT: Thank you to @Akina who suggest me the following solution (it seems to work):

select FIELD1, 
       FIELD2, 
       SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 1 ELSE 0 END) as FIELD3_EQU_32, 
       SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 0 ELSE 1 END) as FIELD3_NEQ_32
from TABLE
group by FIELD1, 
         FIELD2;

What I need now is output only a count who exceed a specif threshold, something like:

       HAVING ((FIELD3_EQU_32 > '10' and SUBSTR(FIELD2, 1,3)='888')  
       or (FIELD3_NEQ_32 > '5' and SUBSTR(FIELD2, 1,3)='999')
etc...

Thank you very much. Lucas

Upvotes: 0

Views: 34

Answers (1)

Akina
Akina

Reputation: 42622

select FIELD1, 
       FIELD2, 
       SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 1 ELSE 0 END) as FIELD3_EQU_32, 
       SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 0 ELSE 1 END) as FIELD3_NEQ_32
from TABLE
group by FIELD1, 
         FIELD2;

Upvotes: 2

Related Questions