CODEWITHSUNDEEP
mysqlsqlcount
Summa
Summa

Reputation: 107

MySQL: How to get a count of events occurred within a span of 24 hrs using ONLY COUNT() and WITHOUT using WHERE clause

This is my scenario:

I am collecting IP addresses of clients pinging my server and also the time of ping in MySQL.

Columns:
IP_address, Time_of_Ping(date - hr - min - sec)

At any given point of time, I want to know how many clients have been communicated with my server in the last 24 hrs.

Please note: I know this can be done with a simple select statement with where clause.

But why I am posting this in the forum just to know: can this be done with just count() I mean just writing some condition within count() and without using where clause?

Thanks in advance

Upvotes: 0

Views: 83

Answers (3)

Summa
Summa

Reputation: 107

I got the expected query and it is working fine:

select count(ping_time > datetime('now', '-24 hours')) from testTable

Upvotes: 0

Nick
Nick

Reputation: 147216

You could take advantage of MySQL treating boolean expressions as either 1 or 0 in a numeric context and use SUM:

SELECT SUM(Time_of_Ping > NOW() - INTERVAL 24 HOUR)
FROM your_table

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270683

I think you want:

select count(*) as num_pings, count(distinct ip_address) as num_ips
from t
where time_of_ping > now() - interval 24 hour;

Note that count(*) is the number of pings. count(distinct) is the number of different ip address, which is what I think you really want.

Upvotes: 0

Related Questions