Aggregations and Grouping

Perform statistical analysis and data summarization using GROUP BY, HAVING, and aggregation functions.

Basic Aggregations

Function	Description	Example
COUNT(*)	Count all rows	SELECT COUNT(*) FROM logs
COUNT(field)	Count non-null values	SELECT COUNT(user_id) FROM logs
SUM(field)	Sum numeric values	SELECT SUM(bytes_sent) FROM logs
AVG(field)	Average of numeric values	SELECT AVG(response_time) FROM logs
MIN(field)	Minimum value	SELECT MIN(timestamp) FROM logs
MAX(field)	Maximum value	SELECT MAX(response_time) FROM logs

-- Basic aggregation examples
SELECT COUNT(*) as total_logs FROM logs;

SELECT AVG(response_time) 
FROM logs 
WHERE service = 'api-gateway';

SELECT 
  MIN(timestamp) as first_log,
  MAX(timestamp) as last_log,
  COUNT(*) as total_count
FROM logs;

GROUP BY

Single Column Grouping

-- Group by single column
SELECT level, COUNT(*) 
FROM logs 
GROUP BY level;

-- Group with aggregations
SELECT 
  service,
  COUNT(*) as request_count,
  AVG(response_time) as avg_response_time
FROM logs 
GROUP BY service
ORDER BY request_count DESC;

Multiple Column Grouping

-- Group by multiple columns
SELECT service, level, COUNT(*) as count
FROM logs 
GROUP BY service, level
ORDER BY service, count DESC;

-- Time-based grouping with multiple dimensions
SELECT 
  service,
  level,
  DATE(timestamp) as log_date,
  COUNT(*) as daily_count
FROM logs 
GROUP BY service, level, DATE(timestamp)
ORDER BY log_date DESC, daily_count DESC;

Advanced Grouping Examples

-- Error rate by service
SELECT 
  service,
  COUNT(*) as total_requests,
  SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) as errors,
  ROUND(SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as error_rate
FROM logs 
WHERE timestamp >= '2024-01-01'
GROUP BY service
ORDER BY error_rate DESC;

-- Response time percentiles by service
SELECT 
  service,
  COUNT(*) as requests,
  MIN(response_time) as min_time,
  AVG(response_time) as avg_time,
  MAX(response_time) as max_time
FROM logs 
WHERE response_time IS NOT NULL
GROUP BY service
ORDER BY avg_time DESC;

HAVING

Use HAVING to filter aggregated results:

-- Filter aggregated results
SELECT service, COUNT(*) as error_count
FROM logs 
WHERE level = 'error'
GROUP BY service
HAVING COUNT(*) > 10;

-- Complex HAVING conditions
SELECT 
  service,
  COUNT(*) as total_requests,
  AVG(response_time) as avg_response_time
FROM logs 
GROUP BY service
HAVING COUNT(*) > 100 
  AND AVG(response_time) > 500
ORDER BY avg_response_time DESC;

-- HAVING with multiple aggregations
SELECT 
  user_id,
  COUNT(*) as log_count,
  COUNT(DISTINCT service) as services_used
FROM logs 
GROUP BY user_id
HAVING COUNT(*) > 50 
  AND COUNT(DISTINCT service) > 3;

Real-World Examples

Service Performance Analysis

-- Comprehensive service performance metrics
SELECT 
  service,
  COUNT(*) as total_requests,
  COUNT(DISTINCT user_id) as unique_users,
  SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) as error_count,
  AVG(response_time) as avg_response_time,
  MIN(response_time) as min_response_time,
  MAX(response_time) as max_response_time
FROM logs 
WHERE timestamp >= '2024-01-01'
GROUP BY service
HAVING COUNT(*) > 100
ORDER BY error_count DESC;

User Activity Patterns

-- User engagement analysis
SELECT 
  user_id,
  COUNT(*) as total_activities,
  COUNT(DISTINCT service) as services_used,
  MIN(timestamp) as first_activity,
  MAX(timestamp) as last_activity,
  COUNT(CASE WHEN level IN ('error', 'warn') THEN 1 END) as issues_encountered
FROM logs 
GROUP BY user_id
HAVING COUNT(*) > 20
ORDER BY total_activities DESC;

Time-Based Analytics

-- Hourly request patterns
SELECT 
  HOUR(timestamp) as hour_of_day,
  COUNT(*) as request_count,
  COUNT(DISTINCT user_id) as active_users,
  AVG(response_time) as avg_response_time
FROM logs 
WHERE timestamp >= '2024-01-01'
GROUP BY HOUR(timestamp)
ORDER BY hour_of_day;

-- Daily error trends
SELECT 
  DATE(timestamp) as log_date,
  service,
  COUNT(*) as total_requests,
  SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) as errors
FROM logs 
WHERE timestamp >= '2024-01-01'
GROUP BY DATE(timestamp), service
HAVING SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) > 0
ORDER BY log_date DESC, errors DESC;

Cross-Service Analysis

-- Service interaction patterns
SELECT 
  source_service,
  target_service,
  COUNT(*) as interaction_count,
  AVG(response_time) as avg_response_time,
  SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END) as error_count
FROM service_logs 
GROUP BY source_service, target_service
HAVING COUNT(*) > 10
ORDER BY interaction_count DESC;

Best Practices

Performance Tips

1. Group by indexed columns when possible for better performance
2. Use HAVING sparingly - filter with WHERE when possible before grouping
3. Limit GROUP BY columns to only what's necessary for your analysis

Query Optimization

-- Good: Filter before grouping
SELECT service, COUNT(*) as error_count
FROM logs 
WHERE level = 'error'          -- Filter early
  AND timestamp >= '2024-01-01'
GROUP BY service
HAVING COUNT(*) > 5;           -- Then filter aggregated results

-- Good: Use meaningful aliases
SELECT 
  service,
  COUNT(*) as total_requests,
  AVG(response_time) as avg_response_ms,
  SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) as error_count
FROM logs 
GROUP BY service;

Common Patterns

Top N Analysis

-- Top 10 most active users
SELECT user_id, COUNT(*) as activity_count
FROM logs 
GROUP BY user_id
ORDER BY activity_count DESC
LIMIT 10;

Threshold-Based Filtering

-- Services with high error rates
SELECT 
  service,
  COUNT(*) as total,
  SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) as errors,
  ROUND(SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as error_rate
FROM logs 
GROUP BY service
HAVING error_rate > 5.0
ORDER BY error_rate DESC;