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

• Geirme: In theory yes, but adding the date check now returns no rows at all

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

enter image description here

db<>fiddle demo

Cheers!!