Oracle query to count and group specific field

Question

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

Akina · Accepted Answer · 2021-05-11 10:50:48Z

2

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;

answered May 11, 2021 at 10:50

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Lucas Rey Over a year ago

Thank you Akina, it seems working fine, please help me now to understand how to archive the following (ALSO REPORTED IN THE FIRST POST). 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')

Akina Over a year ago

@LucasRey The condition by SUBSTR(FIELD2, 1,3) must be placed into WHERE.

Lucas Rey Over a year ago

thank you, but what I need is output only the counter who exceed a specific threshold AND with SUBSTR(FIELD2, 1,3). Example: OUTPUT ONLY FIELD3_EQU_32 > '10' and SUBSTR(FIELD2, 1,3)='888'

Akina Over a year ago

If you need to test the condition by FIELD3_EQU_32 for a list of SUBSTR(FIELD2, 1,3) values then you must add SUBSTR(FIELD2, 1,3) into both output list and GROUP BY expression instead of FIELD2.

Lucas Rey Over a year ago

Ok, but I need complete field2 on output for later analisys. I tried to add: where (FIELD3_EQU_32 > '50' and SUBSTR(FIELD2, 1,3)='888') but I got: "FIELD3_EQU_32": invalid identifier

|

Collectives™ on Stack Overflow

Oracle query to count and group specific field

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related