SQL: Introduction to GROUP BY and Essential Concepts

Setup

1. Create the Database and Table:

   CREATE DATABASE mydatabase;
   USE mydatabase;
   
   CREATE TABLE sales (
       id INT AUTO_INCREMENT PRIMARY KEY,
       product_name VARCHAR(100),
       category VARCHAR(100),
       amount DECIMAL(10, 2),
       sale_date DATE
   );
   
   INSERT INTO sales (product_name, category, amount, sale_date) VALUES
   ('Product A', 'Category 1', 100.00, '2023-10-01'),
   ('Product B', 'Category 1', 150.00, '2023-10-01'),
   ('Product C', 'Category 2', 200.00, '2023-10-01'),
   ('Product A', 'Category 1', 300.00, '2023-10-02'),
   ('Product B', 'Category 1', 250.00, '2023-10-02'),
   ('Product C', 'Category 2', 350.00, '2023-10-02');
   

GROUP BY Basics

1. Summarizing Data by a Single Column

```sql
SELECT category, SUM(amount) AS total_amount
FROM sales
GROUP BY category;
```
-- Output: Total sales amount for each category.

2. Grouped Aggregation with Multiple Columns

```sql
SELECT category, product_name, SUM(amount) AS total_amount
FROM sales
GROUP BY category, product_name;
```
-- Output: Total sales amount for each product within categories.

Using HAVING Clause

3. Filter Grouped Results

```sql
SELECT category, SUM(amount) AS total_amount
FROM sales
GROUP BY category
HAVING total_amount > 500;
```
-- Output: Categories with total sales amount greater than 500.

Practical Example

4. Combined Example – Grouping and Filtering

```sql
SELECT category, product_name, SUM(amount) AS total_amount
FROM sales
WHERE sale_date = '2023-10-01'
GROUP BY category, product_name
HAVING total_amount > 100;
```
-- Output: Products with sales amount greater than 100 on '2023-10-01' grouped by category and product name.

End of Implementation

-- Create a table 'sales' for demonstration purposes
CREATE TABLE sales (
    id INT,
    product_name VARCHAR(50),
    revenue DECIMAL(10, 2),
    sale_date DATE
);

-- Insert sample data into the 'sales' table
INSERT INTO sales (id, product_name, revenue, sale_date) VALUES
(1, 'Product_A', 100.50, '2023-01-01'),
(2, 'Product_B', 200.25, '2023-01-02'),
(3, 'Product_A', 150.00, '2023-01-02'),
(4, 'Product_C', 250.75, '2023-01-03'),
(5, 'Product_B', 300.30, '2023-01-04');

-- Use GROUP BY to aggregate revenue by product_name
SELECT product_name, SUM(revenue) AS total_revenue
FROM sales
GROUP BY product_name;

-- Use GROUP BY and HAVING to filter groups based on a condition
SELECT product_name, SUM(revenue) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(revenue) > 200;

-- Select relevant columns and use GROUP BY
SELECT department, COUNT(employee_id) AS number_of_employees
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 10;

-- Another example filtering based on aggregated data
SELECT product_category, SUM(sales) AS total_sales
FROM sales_data
GROUP BY product_category
HAVING SUM(sales) > 10000;

-- Using multiple columns in GROUP BY and HAVING
SELECT store_location, product_category, AVG(profit) AS average_profit
FROM store_sales
GROUP BY store_location, product_category
HAVING AVG(profit) > 500;

-- Using HAVING with an additional condition
SELECT city, COUNT(order_id) AS total_orders
FROM orders
GROUP BY city
HAVING COUNT(order_id) > 50 AND city != 'New York';

-- Combining HAVING with other aggregates
SELECT customer_id, SUM(purchase_amount) AS total_spent, COUNT(order_id) AS number_of_orders
FROM customer_purchases
GROUP BY customer_id
HAVING SUM(purchase_amount) > 500 AND COUNT(order_id) > 5;

Apply these implementations in your SQL environment to filter grouped data effectively using the HAVING clause. Each snippet performs grouping and then filters the resulting grouped data based on aggregate conditions.

Practical Implementation: Combining GROUP BY and HAVING for Data Analysis

-- Select and group data based on desired columns, calculate aggregate function, and filter groups
SELECT 
    department, 
    AVG(salary) AS average_salary
FROM 
    employees
GROUP BY 
    department
HAVING 
    AVG(salary) > 60000;

-- Group sales data by year and product, calculate the total sales, and filter groups
SELECT 
    year, 
    product, 
    SUM(sales) AS total_sales
FROM 
    sales_data
GROUP BY 
    year, 
    product
HAVING 
    SUM(sales) > 100000;

-- Group customer transactions by customer ID, count the transactions, and filter groups
SELECT 
    customer_id, 
    COUNT(transaction_id) AS transaction_count
FROM 
    transactions
GROUP BY 
    customer_id
HAVING 
    COUNT(transaction_id) > 10;

-- Group orders by customer and order date, compute total order value, and filter 
SELECT 
    customer_id, 
    order_date, 
    SUM(order_value) AS total_order_value
FROM 
    orders
GROUP BY 
    customer_id, 
    order_date
HAVING 
    SUM(order_value) > 500;

-- Group and summarize products by category, calculate the average price, and filter those above a threshold
SELECT 
    category, 
    AVG(price) AS average_price
FROM 
    products
GROUP BY 
    category
HAVING 
    AVG(price) > 50;

These SQL queries illustrate how to effectively use GROUP BY and HAVING together for summarizing and filtering grouped data.

Practical Examples and Case Studies

Example 1: Sales Data Analysis

Objective

Summarize total sales for each product category and filter categories with total sales exceeding $10,000.

SQL Query

SELECT 
    product_category, 
    SUM(sales_amount) AS total_sales
FROM 
    sales
GROUP BY 
    product_category
HAVING 
    SUM(sales_amount) > 10000;

Example 2: Employee Performance Review

Objective

Calculate the average performance score for each department and include only departments with an average score above 75.

SQL Query

SELECT 
    department, 
    AVG(performance_score) AS avg_score
FROM 
    employee_performance
GROUP BY 
    department
HAVING 
    AVG(performance_score) > 75;

Example 3: Customer Order Summary

Objective

Count the number of orders for each customer and exclude those with fewer than 5 orders.

SQL Query

SELECT 
    customer_id, 
    COUNT(order_id) AS number_of_orders
FROM 
    orders
GROUP BY 
    customer_id
HAVING 
    COUNT(order_id) >= 5;

Example 4: Website User Engagement

Objective

Determine the average session duration for each user type and only list those user types with an average of more than 20 minutes.

SQL Query

SELECT 
    user_type, 
    AVG(session_duration) AS avg_session_duration
FROM 
    user_sessions
GROUP BY 
    user_type
HAVING 
    AVG(session_duration) > 20;

Case Study: Retail Store Transactions

Objective

Identify the city-wise total revenue from transactions, and filter cities where total revenue exceeds $50,000.

Example Dataset

SQL Query

SELECT 
    city, 
    SUM(revenue) AS total_revenue
FROM 
    transactions
GROUP BY 
    city
HAVING 
    SUM(revenue) > 50000;

Expected Result

Explanation:

1. New York: 10000 + 50000 = 60000
2. Los Angeles: 20000 (Filtered out as it does not meet the criteria)
3. Chicago: 5000 + 48000 = 53000