Sqlite Counter query

When I put query like counter and simple with (distinct nlri), I got a different number of results with timestamp..although the nature of both queries quite similar. is my counter query regarding timestamp have any problem?? Could someone check my queries…

Basically I want to count the number of results per day based on this checking where nlri like '%/32%' and nlri not like '%:%'

first query with counter:

SELECT COUNT(distinct a.nlri) counter 
FROM (SELECT distinct strftime('%d', tstamp) AS DAY FROM CHECKING) t 
LEFT JOIN CHECKING a  
  ON strftime('%d', a.tstamp) = t.DAY 
  AND (nlri not like ':' AND nlri like '%/32%') 
GROUP BY t.DAY

it gives me result 55

and when I put that query

select distinct nlri 
from CHECKING 
where nlri like '%/32%' 
and nlri not like '%:%' 
and tstamp like '%2021-02-01%'

it gives me result OF 52

I am testing on one day timestamp but I have to apply on multiple days…. dataset like that

tstamp                 nlri
2021-02-01 18:20:46       1.1.1.1/32
2021-02-01 18:22:20       10.101.101.1/32
2021-02-01 17:12:07       4.4.4.4/32
2021-02-01 14:59:05       13.11.11.11/16
2021-02-01 14:59:49       11.3.3.3/16
2021-02-01 15:08:25       13.1.0.0/16

Answer

I really wouldn’t do this using a sub query and left join.

Just group by the timestamp rounded down to a day?

What does this give for you?

SELECT
   STRFTIME('%Y-%m-%d', timestamp) AS day,
   COUNT(DISTINCT nlri) AS counter
FROM
   CHECKING 
WHERE
       nlri     LIKE '%/32%' 
   AND nlri NOT LIKE '%:%' 
GROUP BY
   1
ORDER BY
   1

Advertisement

Answer