Select and sum data from a oracle table containing duplicates

Question

I have a table containing data where some are identical in given columns but different in other columns. I will try to describe this table as follwing:

table1                               table2           table3 
+---+------+-----+----------+-----+  +-----+------+  +------------------+
| id|sub_id|block|block date|valid|  | id  |active|  |rec_id| messageId |                    
|---+------+-----+----------+-----+  +-----+------+  +-------+----------+
|123|123_1 |  0  |          |false|  | 123 |   1  |  | 123   |a123kckcdf|    
|123|123_2 |  0  |          |false|  | 321 |   1  |  | 123   |ffsw2234sd|
|123|123_3 |  1  |01.07.2019|true |  | 456 |   0  |  | 123   |kk3jd563sf|
|321|321_1 |  1  |28.06.2019|true |  | 654 |   1  |  | 654   |89hhrlidlf|
|321|321_2 |  0  |          |false|  +-----+------+  | 456   |jf335698ld|
|456|451_1 |  0  |          |false|                  | 321   |fkd003llds|
|456|456_1 |  0  |          |false|                  | 123   |jdjd234lds|
|654|654_1 |  0  |          |true |                  | 654   |djd34lld23|
|654|654_2 |  1  |01.04.2017|true |                  | 453   |jf33lfrd2l| 
+---+------+-----+----------+-----+                  +-------+----------+

This what I want to achieve:

• Summarize number of records sent to rec_id IF:

  * Block_date has to have occured more than 24 hours ago (presume I will run this query at '01.07.2019')

  * The value 'id' in table1, table2 and rec_id in table3 is identical (and can be used in a join)

  * The value for "valid" in table1 is can be 'false' for all occureences of sub_id (set manually by mistake in application gui)

  * The value for "blacklisted" in table1 must be '1'

  * The value for "active" in table2 must be '1'

  * The time in "block date" in table1 is in timestamp format really and is not truncated (as it seems like in this example)

  * Each message in table3 is a unique message

I would then want to end up with a list like this:

+---------------------+
|  Id  | Num messages |
+------+--------------+
|  321 |     1        |
|  654 |     2        |
+------+--------------+

I have tried with something like this but I have not managed how to get hold of the date period yet and neither how to fully cope with the fact that an records in table1 can be both valid and false and I only want the false ones

SELECT t3.rec_id AS"Id", 
COUNT(t3.rec_id) AS"Num messages" 
FROM TABLE3 t3
INNER JOIN TABLE2 t2
ON t2.id=t3.rec_id
INNER JOIN TABLE1 t1
ON t2.id=t1.id
WHERE t2.active='1' 
AND t1.block='0'
AND t1.valid like 'false'
GROUP BY t3.rec_id
ORDER BY COUNT (t3.rec_id) DESC

(This is to detect faulty messages not beeing detected by our existing routines)

Answer 1

You are almost right, just add below date check for 24 hours.

SELECT t3.rec_id AS"Id", COUNT(t3.rec_id) AS"Num messages" 
FROM TABLE3 t3 INNER JOIN TABLE2 t2
ON t2.id=t3.rec_id
INNER JOIN TABLE1 t1 
ON t2.id=t1.id
WHERE t2.active='1' 
  AND t1.block='0'
  AND t1.valid like 'false'
  AND t1.block_date < sysdate - 1
GROUP BY t3.rec_id
ORDER BY COUNT (t3.rec_id) DESC

Answer 2

Try this:

SELECT
    T1.ID,
    COUNT(1)
FROM
    TABLE1 T1
    JOIN TABLE2 T2 ON ( T1.ID = T2.ID )
    JOIN TABLE3 T3 ON ( T2.ID = T3.REC_ID )
WHERE
    T1.BLOCK_DATE < DATE '2019-07-01' - 1
    AND T1.BLOCK = 1
    AND T2.ACTIVE = 1
GROUP BY
    T1.ID

OUTPUT

db<>fiddle demo

Cheers!!