SQL Tutorial

• What is SQL
• What is RDBMS
• Commonly Used Relational Databases
• SQL Syntax
• SQL Data Types
• SQL CREATE DATABASE
• SQL DROP DATABASE
• SQL USE
• SQL CREATE TABLE
• SQL DROP TABLE
• SQL RENAME TABLE
• SQL INSERT INTO
• SQL SELECT
• SQL WHERE
• SQL AND and OR
• SQL UPDATE
• SQL DELETE
• SQL LIKE
• SQL TOP
• SQL ORDER BY
• SQL GROUP BY
• SQL DISTINCT
• SQL Constraints
• SQL NULL
• SQL NOT NULL
• SQL DEFAULT
• SQL UNIQUE
• SQL Primary Key
• SQL Foreign Key
• SQL CHECK
• SQL INDEX
• SQL JOIN
• SQL INNER JOIN
• SQL LEFT JOIN
• SQL RIGHT JOIN
• SQL FULL JOIN
• SQL SELF JOIN
• SQL CROSS JOIN
• SQL UNION and UNION ALL
• SQL AS
• SQL ALTER TABLE
• SQL TRUNCATE TABLE
• SQL View
• SQL HAVING
• SQL Transaction
• SQL Temporary Tables
• SQL Clone Table
• SQL Sub Query
• SQL AUTO_INCREMENT
• SQL Injection

SQL GROUP BY statement: grouping

The GROUP BY statement in SQL is used in collaboration with the aggregate functions like COUNT(), MAX(), MIN(), SUM(), AVG(), etc. to group the result set by one or more columns.

The basic syntax of the GROUP BY statement is:

SELECT column1, column2, ..., aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

Example:

Consider a Sales table with the following data:

If you want to find the total quantity ordered for each product, you would use the GROUP BY statement as follows:

SELECT Product, SUM(Quantity) 
FROM Sales 
GROUP BY Product;

This query would return:

This shows the total quantity for each product.

Note:

1. The GROUP BY statement is often used with aggregate functions (like COUNT, SUM, AVG, MAX, MIN) to group the result-set by one or more columns.

2. Columns listed in the GROUP BY clause should be either used in aggregate function or should be included in the SELECT clause.

As always, the exact syntax may vary between different SQL dialects, so be sure to consult the documentation for the SQL dialect you're using.

1. Grouping Data with GROUP BY in SQL:

   • Description: Groups rows that have the same values in specified columns into summary rows.
   • Code Example:

     SELECT Column1, COUNT(*)
     FROM TableName
     GROUP BY Column1;
     

2. Aggregating Functions with GROUP BY:

   • Description: Applies aggregate functions (e.g., COUNT, SUM, AVG) to grouped data for summarization.
   • Code Example:

     SELECT Department, AVG(Salary) AS AvgSalary
     FROM Employees
     GROUP BY Department;
     

3. SQL GROUP BY and HAVING Clause:

   • Description: HAVING is used with GROUP BY to filter the results based on aggregate function conditions.
   • Code Example:

     SELECT Department, AVG(Salary) AS AvgSalary
     FROM Employees
     GROUP BY Department
     HAVING AVG(Salary) > 50000;
     

4. GROUP BY vs DISTINCT in SQL:

   • Description: GROUP BY is used for grouping and summarizing data, while DISTINCT is used to retrieve unique values.
   • Code Example (DISTINCT):

     SELECT DISTINCT Column1
     FROM TableName;
     

5. Multiple Columns in GROUP BY Statement:

   • Description: Allows grouping by multiple columns to create more detailed summary rows.
   • Code Example:

     SELECT Department, Gender, AVG(Salary) AS AvgSalary
     FROM Employees
     GROUP BY Department, Gender;
     

6. Sorting Grouped Data with ORDER BY:

   • Description: Sorts the result set based on the grouped columns.
   • Code Example:

     SELECT Department, AVG(Salary) AS AvgSalary
     FROM Employees
     GROUP BY Department
     ORDER BY Department;
     

7. Filtering Grouped Data with HAVING:

   • Description: HAVING is used to filter groups based on conditions.
   • Code Example:

     SELECT Department, AVG(Salary) AS AvgSalary
     FROM Employees
     GROUP BY Department
     HAVING AVG(Salary) > 50000;
     

8. Grouping by Expressions in SQL:

   • Description: Allows grouping by expressions or calculations.
   • Code Example:

     SELECT YEAR(OrderDate) AS OrderYear, COUNT(*) AS OrderCount
     FROM Orders
     GROUP BY YEAR(OrderDate);
     

9. Handling NULL Values with GROUP BY:

   • Description: NULL values are grouped together in the result set.
   • Code Example:

     SELECT Column1, COUNT(*)
     FROM TableName
     GROUP BY Column1;