CODEWITHSUNDEEP
sqloracle-database
emre
emre

Reputation: 45

select without using distinct keyword

I just want to select the ip address if at least 5 different customers have been used same ip for last 7 days. But result is wrong.

table_1
customer ip        date
1        0.0.0.0  15.11.2019
2        0.0.0.0  11.11.2019
3        0.0.0.0  09.11.2019
4        0.0.0.0  10.11.2019

table_2
customer ip       date
1        0.0.0.0  15.11.2019
6        1.2.2.2  11.11.2019
4        0.0.0.0  09.11.2019
8        0.0.0.0  10.11.2019
9        5.5.5.5  12.11.2019

The result should be 0.0.0.0 because customer 1,2,3,4 and 8 uses same ip for last 7 day.

SELECT y.ip,x.customer 
         from table_1 x
         inner join 
         table_2 y
         on y.ip = x.ip
         WHERE x.DATE > SYSDATE - 7
         group by y.ip,y.customer
         Having Count(y.customer)>=5

Upvotes: 3

Views: 80

Answers (2)

GMB
GMB

Reputation: 222482

From your sample data, it looks like your data is spread over the two tables that have the same structure. If so, you want to union all both tables (instead of joining them), and then aggregate:

select t.ip
from (
    select t1.customer, t1.ip, t1.date from table_1 t1
    union all 
    select t2.customer, t2.ip, t2.date from table_2 t2
) t
where t.date > sysdate - 7
group by t.ip
having count(distinct t.customer) >= 5

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

try like below using distinct count

SELECT y.ip
         from table_1 x
         inner join 
         table_2 y
         on y.ip = x.ip
         WHERE x.DATE > SYSDATE - 7
         group by y.ip
         Having Count(distinct y.customer)>=5

Upvotes: 2

Related Questions