CODEWITHSUNDEEP
mysqlsqlselectamazon-athena
Murakami
Murakami

Reputation: 3760

MYSQL in AWS Athena

Using AWS Athena (so querying with MySql) I'm trying to retrieve information how many times the same IP has been logged. I have columns: timestamp, IP, country. I would like to count unique occurrences of each IP.

I'm trying with this query but results are wrong:

SELECT timestamp as Timestamp,
       count(httprequest.clientIp) as Count,
       httprequest.country as Country
FROM table_name
GROUP BY timestamp, httprequest.country

Thank you for helping achieving me this counter query.

EDIT:

Sample data:

{
    "timestamp":1610808650667,"httpRequest": 
               {"clientIp":"11.111.111.111",
                "country":"UK"}
}

Upvotes: 1

Views: 361

Answers (1)

Mureinik
Mureinik

Reputation: 311978

If you only want to know how many times a certain IP has been logged, the IP should be the only column in your group by clause:

SELECT   httprequest.clientIp, COUNT(*)
FROM     table_name
GROUP BY httprequest.clientIp

Upvotes: 1

Related Questions