Impara CASE WHEN with Aggregate Functions | CASE WHEN for Aggregates and Reporting

When you want to perform grouped analysis in SQL, aggregate functions like SUM, COUNT, and AVG let you summarize data. However, sometimes you need to calculate these summaries only for certain rows within each group. This is where combining CASE WHEN with aggregate functions becomes powerful: you can add conditions inside your aggregates to count, sum, or average only the rows that match your criteria.


              123456
            
SELECT
  customer_id,
  SUM(CASE WHEN transaction_type = 'purchase' THEN amount ELSE 0 END) AS total_purchases,
  SUM(CASE WHEN transaction_type = 'deposit' THEN amount ELSE 0 END) AS total_deposits
FROM transactions
GROUP BY customer_id;

This query demonstrates how conditional aggregation works. By placing a CASE WHEN expression inside SUM, you control which rows contribute to the total. For each customer_id, the query calculates the total amount of purchases and deposits separately. If the transaction_type matches the condition (such as 'purchase'), the amount is included in the sum; otherwise, zero is added. This approach lets you create separate totals for different transaction types in a single grouped query, making your reports more precise and flexible.


              123
            
SELECT
  COUNT(CASE WHEN amount > 100 THEN 1 END) AS transactions_above_100
FROM transactions;

1. What is the effect of using CASE WHEN inside an aggregate function?

2. Fill in the blanks to calculate the total amount of 'refund' transactions using SUM and CASE WHEN:

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 4. Capitolo 1

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Scorri per mostrare il menu


              123456
            
SELECT
  customer_id,
  SUM(CASE WHEN transaction_type = 'purchase' THEN amount ELSE 0 END) AS total_purchases,
  SUM(CASE WHEN transaction_type = 'deposit' THEN amount ELSE 0 END) AS total_deposits
FROM transactions
GROUP BY customer_id;


              123
            
SELECT
  COUNT(CASE WHEN amount > 100 THEN 1 END) AS transactions_above_100
FROM transactions;

1. What is the effect of using CASE WHEN inside an aggregate function?

2. Fill in the blanks to calculate the total amount of 'refund' transactions using SUM and CASE WHEN:

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 4. Capitolo 1