SQL Clauses
Duration : 00:03:00
Distinct clause with a single expression
The DISTINCT clause is used to eliminate duplicate rows from the result set based on a single expression or column.Example
SELECT DISTINCT column1
FROM table_name;
Distinct clause with multiple expressions
You can also use DISTINCT with multiple expressions or columns, ensuring that the combination of values is unique.Example
SELECT DISTINCT column1, column2
FROM table_name;
SQL Server GROUP BY Clause
The GROUP BY clause is used to group rows from a table based on one or more columns. It is often used with aggregate functions to summarize data.Example
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
GROUP By using the SUM Function clause
When using GROUP BY with the SUM function, you can calculate the sum of values for each group.Example
SELECT department, SUM(salary)
FROM employees
GROUP BY department;
GROUP By using the COUNT Function clause
With GROUP BY and the COUNT function, you can count the number of rows in each group.Example
SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department;
GROUP By using the MIN Function clause
Using GROUP BY with the MIN function allows you to find the minimum value in each group.Example
SELECT department, MIN(salary)
FROM employees
GROUP BY department;
GROUP By using the MAX Function clause
GROUP BY with the MAX function helps you find the maximum value in each group.Example
SELECT department, MAX(salary)
FROM employees
GROUP BY department;
SQL Server WHERE Clause
The WHERE clause is used to filter rows in a table based on a specified condition.Example
SELECT
FROM orders
WHERE order_date >= '2023-01-01';
SQL Server ORDER BY Clause
The ORDER BY clause is used to sort the result set based on one or more columns in ascending or descending order.Example
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC;
OFFSET and FETCH options in the ORDER BY Clause
OFFSET and FETCH are used with ORDER BY to implement pagination. OFFSET skips a specified number of rows, and FETCH limits the number of rows returned.Example
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
SQL Server HAVING Clause
The HAVING clause is used in conjunction with GROUP BY to filter groups based on aggregate conditions.Example
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
SQL Server SELECT Clause
The SELECT clause is used to specify which columns to include in the result set.Example
SELECT first_name, last_name
FROM employees;
SQL Server GROUPING SETS Clause
GROUPING SETS is used to group data based on multiple sets of columns, providing more flexible grouping options.Example
SELECT department, region, SUM(sales)
FROM sales_data
GROUP BY GROUPING SETS ((department), (region), ());
GROUPING SETS Equivalents clause
GROUPING SETS can also be achieved using the UNION ALL operator to combine multiple GROUP BY queries.Example
-- Calculate total sales amounts for different groupings using GROUPING SETS
SELECT Region, Product, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ((Region), (Product), ());
Still have some questions? Let's discuss.
CONTACT US