270. Aggregate functions

I say grouping, you say …

In this lesson we’ll review the aggregate functions we’ve used in the course:

COUNT()

The COUNT() function counts all records in the aggregated group:

SELECT country, COUNT(*) AS users_count FROM users GROUP BY 1 ORDER BY 2 DESC 

To count records only once we can add DISTINCT() statement:

SELECT product_id, COUNT(DISTINCT(user_id)) AS customers_total FROM purchases GROUP BY 1 ORDER BY 2 DESC 

If we need something more advanced we can use if/else logic:

SELECT COUNT(CASE WHEN age > 65 THEN id END) AS seniors_count...