Where we can use aggregate functions

Recap:

• SELECT with group by: To aggregate values and show results.
• HAVING: To filter groups based on aggregate values.
• ORDER BY: To sort results based on aggregated values.
• Subqueries (in WHERE/FROM): To aggregate data in a subquery and filter or join based on it.
• Window Functions (OVER): To calculate aggregate functions over a specific window of rows without grouping them.
• CASE: To perform conditional aggregation based on certain criteria.

Feel free to ask about any specific usage or examples you want to dive deeper into!

1. In the SELECT clause:

You can use aggregate functions to summarize or calculate values based on grouped rows.

Example:

SELECT 
    customer_id,
    SUM(total_amount) AS total_spent
FROM 
    orders
GROUP BY 
    customer_id;

• Here, SUM(total_amount) is calculating the total spent per customer.

2. In the HAVING clause:

The HAVING clause filters the result after grouping rows, and you can use aggregate functions to apply conditions on grouped data.

Example:

SELECT 
    customer_id,
    COUNT(*) AS order_count
FROM 
    orders
GROUP BY 
    customer_id
HAVING 
    COUNT(*) > 1;

• Here, COUNT(*) is used in the HAVING clause to filter customers who have placed more than 1 order.

3. In the ORDER BY clause:

You can use aggregate functions to sort the result based on calculated values.

Example:

SELECT 
    customer_id,
    SUM(total_amount) AS total_spent
FROM 
    orders
GROUP BY 
    customer_id
ORDER BY 
    total_spent DESC;

• This query sorts the customers by their total spent amount in descending order.

4. In a WHERE clause with subqueries:

Although aggregate functions are not directly used in the WHERE clause, you can use them in subqueries that are part of the WHERE clause.

Example:

SELECT 
    customer_id,
    total_amount
FROM 
    orders
WHERE 
    customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 1);

• Here, the subquery is using COUNT() to filter customers with more than one order, and the outer query selects those customers’ orders.

5. In OVER() (Window Functions):

You can also use aggregate functions with window functions using the OVER() clause, allowing you to calculate values across a partition of data without collapsing rows.

Example:

SELECT 
    order_id,
    customer_id,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM 
    orders;

• This example uses SUM() as a window function to calculate a running total for each customer, ordered by order_date.

6. In the FROM clause (Subquery or Derived Tables):

You can use aggregate functions in a subquery within the FROM clause.

Example:

SELECT 
    customer_id,
    total_spent
FROM 
    (SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id) AS customer_summary
WHERE 
    total_spent > 100;

• In this example, SUM(total_amount) is used in the subquery to aggregate data, and the outer query filters customers with total spent greater than 100.

7. In CASE statements:

You can use aggregate functions inside CASE statements to apply conditional logic.

Example:

SELECT 
    customer_id,
    SUM(CASE WHEN order_date >= '2024-01-01' THEN total_amount ELSE 0 END) AS new_year_spent
FROM 
    orders
GROUP BY 
    customer_id;

• Here, CASE is used to sum only the total_amount for orders placed after January 1st, 2024.