Day 7: Grouping Data with GROUP BY and Filtering with HAVING

Day 7: Grouping Data with GROUP BY and Filtering with HAVING 

    1. Using GROUP BY
            The GROUP BY statement groups rows sharing a common value, often used with aggregate functions.

        Syntax:

            SELECT Column1, AggregateFunction(Column2) FROM TableName GROUP BY Column1;

    Example:

        SQL

        -- Total salary per department
        SELECT Department, SUM(Salary) AS TotalSalary
        FROM Employees
        GROUP BY Department;

    2. Filtering Groups with HAVING

            Use HAVING to filter groups created by GROUP BY.
            Similar to WHERE, but for aggregated data.  

            Example:

                SQL

            -- Departments with total salary > 100000
            SELECT Department, SUM(Salary) AS TotalSalary
            FROM Employees
            GROUP BY Department
            HAVING SUM(Salary) > 100000;

    3. Combining WHERE, GROUP BY, and HAVING

            Use WHERE to filter rows before grouping.
            Use HAVING to filter groups after aggregation.

            Example:

                SQL

            -- Total salary of IT department with salary > 40000
            SELECT Department, SUM(Salary) AS TotalSalary
            FROM Employees
            WHERE Salary > 40000
            GROUP BY Department
            HAVING SUM(Salary) > 100000;

    4. Sorting Groups with ORDER BY

           Sort grouped data using ORDER BY.

           Example:

                SQL

            -- Sort by total salary (descending)
            SELECT Department, SUM(Salary) AS TotalSalary
            FROM Employees
            GROUP BY Department
            ORDER BY TotalSalary DESC;

Action Steps

1. Group data by a column (e.g., department) and use aggregate functions.
2. Filter groups using HAVING.
3. Sort grouped data with ORDER BY.

These are very useful SQL concepts, so I would recommend you solve problems related to GROUP BY & HAVING from leetcode or Stratascrach today itself. Start with easy ones and increase difficulty level as you proceed.