COUNT

Counts the number of rows or non-null values. Ideal for answering questions like "How many users signed up last month?" or "How many videos received comments?". Use COUNT(*) for total rows or COUNT(column) to count non-null values in a specific column. COUNT(DISTINCT column) counts unique non-null values.

SUM

Calculates the total sum of values in a numeric column. Use this to find metrics like total views across all videos, total revenue generated, or total time spent watching content.

AVG

Computes the average (mean) value of a numeric column. Answers questions like "What's the average number of views per TokTuk video?" or "What's the average number of likes per user?". NULL values are typically ignored in the calculation.

MIN and MAX

Find the minimum (smallest) and maximum (largest) values in a column. Useful for identifying extremes: the most/least viewed video, the earliest/latest signup date, the highest/lowest interaction count. Works on numeric, date, and text columns.

GROUP BY

Groups rows that share the same values in one or more specified columns into a summary row. Aggregate functions (COUNT, SUM, AVG, etc.) are then applied to each group independently. This is crucial for segmented analysis, like finding the total views per user or the number of interactions per video category.

HAVING

Filters the results of a GROUP BY query based on the aggregated values. While the WHERE clause filters rows *before* grouping, HAVING filters the groups *after* aggregation. Use this to answer questions like "Which users have uploaded more than 5 videos?" or "Which video categories have an average view count above 1000?".

CASE WHEN

Adds conditional logic (if-then-else) within your SQL query. While not strictly an aggregation function, it's incredibly useful when combined with GROUP BY. You can use CASE WHEN to categorize data before aggregation (e.g., grouping videos into "Short", "Medium", "Long" based on duration) or to perform conditional aggregation (e.g., counting only "like" interactions).

-- Categorize videos by view count and count videos in each category
SELECT
  CASE
    WHEN views < 1000 THEN 'Low Views'
    WHEN views BETWEEN 1000 AND 10000 THEN 'Medium Views'
    ELSE 'High Views'
  END AS view_category,
  COUNT(*) AS video_count,
  AVG(views) AS avg_views_in_category
FROM Videos
GROUP BY view_category;

-- Count likes vs comments per video
SELECT
  video_id,
  COUNT(CASE WHEN interaction_type = 'like' THEN 1 END) AS like_count,
  COUNT(CASE WHEN interaction_type = 'comment' THEN 1 END) AS comment_count
FROM Interactions
GROUP BY video_id;