An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria, to form a single value of more significant meaning or measurement.

Function: avg(numeric_expr)

This function returns the average of the values for a group of rows computed by numeric_exp. NULL value rows are not included in the calculation.

SELECT customer_user_id, avg(amount_spent) as avg_spent
FROM cooladata
WHERE date_range(all)
GROUP BY customer_user_id

Function: count(*)

This function returns the total number of values (including NULLs) in the scope of the function.

SELECT count(*)
FROM cooladata
WHERE date_range(all)

Function: count([DISTINCT field [, exact])

This function returns the total number of non-NULL values in the scope of the function. If you use the DISTINCT keyword, the function returns the number of unique values in the specified field. If the "exact" argument is requested, exact computation will occur and no estimation will take place.

SELECT count(distinct user_id)
FROM cooladata
WHERE date_range(all)

Function: stddev(numeric_expr)

This function returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

SELECT stddev(amont_spent) as stddev
FROM cooladata
WHERE date_range(all)

Function: variance(numeric_expr)

This function returns the variance of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

SELECT variance(amount_spent) as spent_variance
FROM cooladata
WHERE date_range(all)

Function: max(field)

This function returns the maximum value in the scope of the function.

SELECT customer_user_id, max(amount_spent) as max_spent
FROM cooladata
WHERE year(event_time) = 2013
GROUP BY customer_user_id
ORDER BY spent DESC
LIMIT 100

Function: min(field)

This function returns the minimum value in the scope of the function.

SELECT customer_user_id, datediff(max(event_time_ts),min(event_time_ts)) as engagement_duration
FROM cooladata
WHERE date_range(all)
GROUP BY customer_user_id
ORDER BY engagement_duration
LIMIT 100

Function: sum(field)

This function returns the sum total of the values in the scope of the function. Only numeric data typed fields may be used with this function.

SELECT count(session_id) AS session_count, sum(amount_spent) as revenue
FROM cooladata
WHERE date_range(all)