Dear community,
could someone help me to join 2 queries into one?
Basically it's the same query with different clauses (please notice the FIELD3 filters and related counters into the subquery):
SELECT A.FIELD1,A.FIELD2,A.FIELD3
FROM
TABLE A
INNER JOIN
(
SELECT FIELD1, COUNT(1)
FROM TABLE
where SUBSTR(FIELD3,1,5)='33377' and
timestamp between sysdate - interval '20' minute and sysdate - interval '2' minute
GROUP BY FIELD1
HAVING COUNT(1) >= 100
) B
ON A.FIELD1 = B.FIELD1 where ...some other clauses who interacts with FIELD4,5,6,etc... ;
SELECT A.FIELD1,A.FIELD2,A.FIELD3
FROM
TABLE A
INNER JOIN
(
SELECT FIELD1, COUNT(1)
FROM TABLE
where SUBSTR(FIELD3,1,5) != '33377' and
timestamp between sysdate - interval '20' minute and sysdate - interval '2' minute
GROUP BY FIELD1
HAVING COUNT(1) >= 150
) B
ON A.FIELD1 = B.FIELD1 where ...some other clauses who interacts with FIELD4,5,6,etc... ;
What's my goal? Let me explain better.
I have an Oracle DB with a table with some fields, for this query a need only 3 fields as shown below.
I'm trying to make a query with multiple counts. What I need is a query that output a list who exceeded a specific count based on the first 5 digits of FIELD3. Let me do a specific example:
This is what I have into the DB:
FIELD1 FIELD2 FIELD3
1234567314 333776543585218 333771434591151
1234567871 333771451776784 333771432365581
1234567314 333776543585218 333771240553976
1234567314 333776543585218 333773861003473
1234567314 333776543585218 333773861003473
1234567314 333776543585218 333023861003473
1234567314 333776543585218 333023861003473
1234567314 333776543585218 333023861003473
1234567337 333773660813075 333773650804767
1234567137 333773660798439 333771222628311
1234567319 333776543585219 333773660667594
1234567314 333776543585218 333901451463400
1234567314 333776543585218 333901451463400
Now, I want to output numbers in field1 in the following way:
OUTPUT FIELD1 (and related FIELD2 and FIELD3) who are exceeded a COUNT1 (EG: 3) based on FIELD3 having the same 5 first digits (33377)
OUTPUT FIELD1 (and related FIELD2 and FIELD3) who are exceeded a COUNT2 (EG: 10) based on FIELD3 NOT having the same 5 first digits (33377)
So, in the upper example, my output will be:
1234567314 333776543585218 333771434591151
1234567314 333776543585218 333771240553976
1234567314 333776543585218 333773861003473
1234567314 333776543585218 333773861003473
1234567314 333776543585218 333023861003473
1234567314 333776543585218 333023861003473
1234567314 333776543585218 333023861003473
1234567314 333776543585218 333901451463400
1234567314 333776543585218 333901451463400
33377 = 4 occurencies
Others = 5 occurences
First threshold exceeded the count so, report all rows.
Basically the 2 queries reported above works perfect, but I would like to join them into one to minimize query time and have an unique output.
Many thanks.
Lucas