Aggregate Functions
Aggregate functions are used to perform calculations on a set of values and return a single value. It is often used with the GROUP BY clause to calculate the aggregate values for each group.
Here are some of the most commonly used aggregate functions:
- - COUNT(): returns the number of rows in a group.
- - SUM(): returns the sum of all values in a group.
- - AVG(): returns the average of all values in a group.
- - MIN(): returns the minimum value in a group.
- - MAX(): returns the maximum value in a group.
| product_id | product_name | unit_price | quantity |
|---|---|---|---|
| 1 | Apple | 1.00 | 10 |
| 2 | Orange | 0.50 | 20 |
| 3 | Banana | 0.75 | 15 |
Let's see some examples of using aggregate functions:
Example 1: Using COUNT()
Count the number of products in the table.
SELECT COUNT(*) AS total_products
FROM products;
| total_products |
|---|
| 3 |
Example 2: Using SUM()
Calculate the total price of all products in the table.
SELECT SUM(unit_price * quantity) AS total_price
FROM products;
| total_price |
|---|
| 25.00 |
Example 3: Using AVG()
Calculate the average unit price of all products in the table.
SELECT AVG(unit_price) AS avg_price
FROM products;
| avg_price |
|---|
| 0.75 |
Example 4: Using MIN()
Find the minimum unit price of all products in the table.
SELECT MIN(unit_price) AS min_price
FROM products;
| min_price |
|---|
| 0.50 |
Example 5: Using MAX()
Find the maximum unit price of all products in the table.
SELECT MAX(unit_price) AS max_price
FROM products;
| max_price |
|---|
| 1.00 |
Scalar Functions
Scalar functions are functions that return a single value based on the input values. They can be used in SELECT statements, WHERE clauses, ORDER BY clauses, and other parts of SQL queries.
Here are some of the most commonly used scalar functions:
- - UPPER(): converts a string to uppercase.
- - LOWER(): converts a string to lowercase.
- - LENGTH(): returns the length of a string.
- - CONCAT(): concatenates two or more strings.
- - SUBSTRING(): extracts a substring from a string.
- - ROUND(): rounds a number to a specified number of decimal places.
Let's see some examples of using scalar functions:
Example 1: Using UPPER()
Convert the product names to uppercase.
SELECT UPPER(product_name) AS product_name_upper
FROM products;
| product_name_upper |
|---|
| APPLE |
| ORANGE |
| BANANA |
Example 2: Using LENGTH()
Find the length of the product names.
SELECT product_name, LENGTH(product_name) AS name_length
FROM products;
| product_name | name_length |
|---|---|
| Apple | 5 |
| Orange | 6 |
| Banana | 6 |
Example 3: Using CONCAT()
Concatenate the product names and unit prices.
SELECT CONCAT(product_name, ' - $', unit_price) AS product_info
FROM products;
| product_info |
|---|
| Apple - $1.00 |
| Orange - $0.50 |
| Banana - $0.75 |
Example 4: Using ROUND()
Round the unit prices to two decimal places.
SELECT product_name, ROUND(unit_price, 2) AS rounded_price
FROM products;
| product_name | rounded_price |
|---|---|
| Apple | 1.00 |
| Orange | 0.50 |
| Banana | 0.75 |
Example 5: Using SUBSTRING()
Extract the first three characters of the product names.
SELECT product_name, SUBSTRING(product_name, 1, 3) AS short_name
FROM products;
| product_name | short_name |
|---|---|
| Apple | App |
| Orange | Ora |
| Banana | Ban |
Example 6: Using ROUND()
Round the unit prices to two decimal places.
SELECT product_name, ROUND(unit_price, 2) AS rounded_price
FROM products;
| product_name | rounded_price |
|---|---|
| Apple | 1.00 |
| Orange | 0.50 |
| Banana | 0.75 |
Date and Time functions
SQL provides a variety of functions to work with date and time values. These functions can be used to extract parts of a date or time, perform calculations on dates, and format date and time values.
Here are some of the most commonly used date and time functions:
- - CURRENT_DATE: returns the current date.
- - CURRENT_TIME: returns the current time.
- - CURRENT_TIMESTAMP: returns the current date and time.
- - DATE(): extracts the date part of a datetime value.
- - TIME(): extracts the time part of a datetime value.
- - YEAR(): extracts the year part of a date or datetime value.
- - MONTH(): extracts the month part of a date or datetime value.
- - DAY(): extracts the day part of a date or datetime value.
- - HOUR(): extracts the hour part of a time or datetime value.
- - MINUTE(): extracts the minute part of a time or datetime value.
- - SECOND(): extracts the second part of a time or datetime value.
- - DATE_FORMAT(): formats a date or time value.
Let's see some examples of using date and time functions:
Example 1: Using CURRENT_DATE
Get the current date.
SELECT CURRENT_DATE AS current_date;
| current_date |
|---|
| 2022-01-01 |
Example 2: Using CURRENT_TIME
Get the current time.
SELECT CURRENT_TIME AS current_time;
| current_time |
|---|
| 12:00:00 |
Example 3: Using CURRENT_TIMESTAMP
Get the current date and time.
SELECT CURRENT_TIMESTAMP AS current_datetime;
| current_datetime |
|---|
| 2022-01-01 12:00:00 |
Example 4: Using DATE()
Extract the date part of a datetime value.
SELECT order_date, DATE(order_date) AS order_date_only
FROM orders;
| order_date | order_date_only |
|---|---|
| 2022-01-01 12:00:00 | 2022-01-01 |
| 2022-01-02 13:00:00 | 2022-01-02 |
| 2022-01-03 14:00:00 | 2022-01-03 |
Example 5: Using DATE_FORMAT()
Format the order dates in 'YYYY-MM-DD' format.
SELECT order_date, DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;
| order_date | formatted_date |
|---|---|
| 2022-01-01 12:00:00 | 2022-01-01 |
| 2022-01-02 13:00:00 | 2022-01-02 |
| 2022-01-03 14:00:00 | 2022-01-03 |
Example 6: Using YEAR()
Extract the year part of a date or datetime value.
SELECT order_date, YEAR(order_date) AS order_year
FROM orders;
| order_date | order_year |
|---|---|
| 2022-01-01 12:00:00 | 2022 |
| 2022-01-02 13:00:00 | 2022 |
| 2022-01-03 14:00:00 | 2022 |
Example 7: Using MONTH()
Extract the month part of a date or datetime value.
SELECT order_date, MONTH(order_date) AS order_month
FROM orders;
| order_date | order_month |
|---|---|
| 2022-01-01 12:00:00 | 1 |
| 2022-01-02 13:00:00 | 1 |
| 2022-01-03 14:00:00 | 1 |
Example 8: Using DAY()
Extract the day part of a date or datetime value.
SELECT order_date, DAY(order_date) AS order_day
FROM orders;
| order_date | order_day |
|---|---|
| 2022-01-01 12:00:00 | 1 |
| 2022-01-02 13:00:00 | 2 |
| 2022-01-03 14:00:00 | 3 |
Example 9: Using HOUR()
Extract the hour part of a time or datetime value.
SELECT order_time, HOUR(order_time) AS order_hour
FROM orders;
| order_time | order_hour |
|---|---|
| 12:00:00 | 12 |
| 13:00:00 | 13 |
| 14:00:00 | 14 |
Example 10: Using MINUTE()
Extract the minute part of a time or datetime value.
SELECT order_time, MINUTE(order_time) AS order_minute
FROM orders;
| order_time | order_minute |
|---|---|
| 12:00:00 | 0 |
| 13:00:00 | 0 |
| 14:00:00 | 0 |
Example 11: Using SECOND()
Extract the second part of a time or datetime value.
SELECT order_time, SECOND(order_time) AS order_second
FROM orders;
| order_time | order_second |
|---|---|
| 12:00:00 | 0 |
| 13:00:00 | 0 |
| 14:00:00 | 0 |
Using DATE_FORMAT()
Format the order dates in 'YYYY-MM-DD' format.
SELECT order_date, DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;
| order_date | formatted_date |
|---|---|
| 2022-01-01 12:00:00 | 2022-01-01 |
| 2022-01-02 13:00:00 | 2022-01-02 |
| 2022-01-03 14:00:00 | 2022-01-03 |