count(distinct request_ip) GROUP by date

I have the following SQL, which I am using against AWS ELB logs stored in S3 with AWS Athena.

SELECT count(distinct request_ip) AS
 count,
 request_ip,
 DATE(from_iso8601_timestamp(timestamp)) AS date
FROM "default"."aws_elb_logs"
WHERE request_ip = '10.0.2.1'
GROUP BY request_ip, DATE(from_iso8601_timestamp(timestamp))

when i run the query, i get

request_ip  total   date
1   10.0.2.1    1   2020-12-24
2   10.0.2.1    1   2020-12-25

how do i count the total IPs and group these by date for a specific IP?

any advise is much appreciated

Answer

Try this script.

SELECT count(request_ip) AS count,
       DATE(from_iso8601_timestamp(timestamp)) AS date
FROM "default"."aws_elb_logs"
WHERE request_ip = '10.0.2.1'
GROUP BY DATE(from_iso8601_timestamp(timestamp))

Advertisement

Answer