Aggregate Functions

Operate on a set of values to compute a single result.

The SQL API supports the following aggregate functions:

SELECT [ MIN | MAX ]( {columns,...} ) ... 

SELECT [ COUNT [DISTINCT] | AVG | SUM ]( {columns,...} ) ...

📘
The COUNT and DISTINCT operations are exact counts. DISTINCT is limited to a maximum of 1 million distinct values.

Examples

SELECT AVG(request_processing_time),client_ip from "my-elb-view" GROUP BY client_ip LIMIT 3;

AVG(request_processing_time)	client_ip
3.2E-5												10.135.89.98
7.4E-5												10.242.147.35
8.4E-5												10.100.224.176

SELECT sum(sent_bytes) as total_sent, client_ip 
FROM "sample-elb-view"
GROUP BY client_ip
HAVING sum(sent_bytes) > '50000'
ORDER BY total_sent DESC LIMIT 5;

total_sent			client_ip
99119.0					10.124.150.47
89698.0					10.112.79.174
82119.0					10.9.79.3
80997.0					10.242.147.35
74130.0					10.231.169.178

SELECT count("Records.eventName") FROM "cloudtrail-xl-view"

count("Records.eventName")
53243


SELECT count(DISTINCT "Records.eventName") FROM "cloudtrail-xl-view"

count(DISTINCT "Records.eventName")
207

Updated 6 months ago