CODEWITHSUNDEEP
mysqlsqlselect
user1928099
user1928099

Reputation: 55

SQL ,How to count rows in range

I have two table:

One is a_tbl(uid, login_ip, login_time);

here ip is a 32 bit integer, time is the unix timestamp.

another is b_tbl(country, province, city, ip_from, ip_to)

I want to find out that each range in b_tbl has how many unique uid in a_tbl.

Notice that b_tbl has a lot of unique rows, and a_tbl may exist dunplicated uid.

Thank you very much.

Upvotes: 3

Views: 189

Answers (3)

e-mre
e-mre

Reputation: 3383

SELECT city, count(uid)
FROM (
  SELECT DISTINCT A.uid, B.city
  FROM a_tbl A 
       LEFT JOIN b_tbl B 
                 ON A.login_ip >= B.ip_from AND A.login_ip <= B.ip_to
)
GROUP BY city

Upvotes: 0

Umed Jan
Umed Jan

Reputation: 63

try this: 

SELECT ip_from
    , ip_to
    , COUNT(distinct uid) as NumberOfUniqueUserId
FROM a_tbl
INNER JOIN b_tbl on login_ip BETWEEN ip_from AND ip_to
GROUP BY ip_from
    , ip_to

Upvotes: 1

MikeB
MikeB

Reputation: 1547

To implement this, you can write a JOIN using BETWEEN as its predicate. That will develop the ranges; then, just count the items falling in each range. Something like this should get you started:

SELECT b_tbl.ip_From, b_tbl.ip_to, COUNT(a_tbl.login_ip)
  FROM a_tbl
  JOIN b_tbl ON a_tbl.login_ip BETWEEN b_tbl.ipfrom AND b_tbl.ip_to
GROUP BY  b_tbl.ip_From, b_tbl.ip_to;

Upvotes: 2

Related Questions