Group Functions in SQL
In SQL, group functions help us perform calculations on groups of rows in a database table. These functions allow us to summarize and retrieve information in a meaningful way. Here are some common group functions with examples and SQL codes:
Various Group Functions
- COUNT()
- Counts the number of rows in a group.Example: Count the number of employees in a department.
SELECT department, COUNT(*) FROM employees GROUP BY department; - SUM()
- Calculates the sum of values in a group.Example: Calculate the total salary of employees in a department
SELECT department, SUM(salary) FROM employees GROUP BY department; - AVG()
- Computes the average value of a column in a group.Example: Find the average age of customers in a city.
SELECT city, AVG(age) FROM customers GROUP BY city; - MAX()
- Retrieves the maximum value in a group.Example: Find the highest score in each subject.
SELECT subject, MAX(score) FROM exam_scores GROUP BY subject; - MIN()
- Retrieves the minimum value in a group.Example: Find the lowest temperature in each city.
SELECT city, MIN(temperature) FROM weather_data GROUP BY city;
Summary
- Group functions in SQL help us perform calculations on groups of data.
- COUNT() counts rows, SUM() adds up values, AVG() finds the average, MAX() gets the highest, and MIN() retrieves the lowest value.
- They are often used with the GROUP BY clause to group data by a specific column.
- These functions are useful for generating meaningful insights and reports from large datasets.
| Function | Description | Example |
|---|---|---|
| COUNT() | Count rows in a group | SELECT department, COUNT(*) FROM employees GROUP BY department; |
| SUM() | Sum values in a group | SELECT department, SUM(salary) FROM employees GROUP BY department; |
| AVG() | Calculate average in a group | SELECT city, AVG(age) FROM customers GROUP BY city; |
| MAX() | Find maximum in a group | SELECT subject, MAX(score) FROM exam_scores GROUP BY subject; |
| MIN() | Find minimum in a group | SELECT city, MIN(temperature) FROM weather_data GROUP BY city; |
String Functions in SQL
String functions in SQL are special operations that allow you to manipulate and work with text or character data in a database. These functions help you perform tasks like extracting specific parts of a string, changing the case of text, or combining multiple strings together.
Here are some common string functions in SQL, along with examples and SQL code:
1. LEN()
- Purpose: Calculates the length (number of characters) of a string.
- Example:
SELECT LEN('Hello, World!') AS Length;Output:Length: 13
2. UPPER()
- Purpose: Converts all characters in a string to uppercase.
- Example:
SELECT UPPER('Hello, World!') AS Uppercase;Output:Uppercase: HELLO, WORLD!
3. LOWER()
- Purpose: Converts all characters in a string to lowercase.
- Example:
SELECT LOWER('Hello, World!') AS Lowercase;Output:Lowercase: hello, world!
4. SUBSTRING()
- Purpose: Extracts a portion of a string based on the specified starting position and length.
- Example:
SELECT SUBSTRING('Hello, World!', 7, 5) AS Substring;Output:Substring: World
5. CONCAT()
- Purpose: Combines two or more strings together.
- Example:
SELECT CONCAT('Hello', ', ', 'World!') AS Concatenated;Output:Concatenated: Hello, World!
6. REPLACE()
- Purpose: Replaces all occurrences of a specified substring with another substring.
- Example:
SELECT REPLACE('Hello, World!', 'World', 'Universe') AS Replaced;Output:Replaced: Hello, Universe!
7. TRIM()
- Purpose: Removes leading and trailing spaces from a string.
- Example:
SELECT TRIM(' Hello, World! ') AS Trimmed;Output:Trimmed: Hello, World!
8. CHARINDEX()
- Purpose: Finds the position of a substring within a string.
- Example:
SELECT CHARINDEX('World', 'Hello, World!') AS Position;Output:Position: 7
Summary:
| Function | Purpose | Example |
|---|---|---|
LEN() | Calculate string length | SELECT LEN('Hello, World!') |
UPPER() | Convert to uppercase | SELECT UPPER('Hello, World!') |
LOWER() | Convert to lowercase | SELECT LOWER('Hello, World!') |
SUBSTRING() | Extract part of a string | SELECT SUBSTRING('Hello, World!', 7, 5) |
CONCAT() | Combine strings | SELECT CONCAT('Hello', ', ', 'World!') |
REPLACE() | Replace text in a string | SELECT REPLACE('Hello, World!', 'World', 'Universe') |
TRIM() | Remove leading/trailing spaces | SELECT TRIM(' Hello, World! ') |
CHARINDEX() | Find position of substring | SELECT CHARINDEX('World', 'Hello, World!') |
String functions in SQL are handy tools for working with text data, allowing you to manipulate and extract information from strings stored in your database.
