Functions used in query like sum, average, max, min, count etc.
Functions used in query like sum, average, max, min, count etc.
Embedded Files
Functions like sum, average, max, min, count, and others are commonly used in various database query languages, such as SQL, to perform calculations and aggregations on data. Here's a brief overview of these functions:
1.SUM: The SUM function is used to calculate the total of a numeric column in a database table. For example, you can use it to find the total sales for a particular product.
SELECT SUM(sales_amount) FROM sales_data;
2.AVERAGE (AVG): The AVERAGE function, often abbreviated as AVG, calculates the average value of a numeric column.
SELECT AVG(temperature) FROM weather_data
3.MAX: The MAX function returns the maximum value in a specified column. It is useful for finding the highest value in a dataset.
SELECT MAX(score) FROM exam_scores;
4.MIN: The MIN function returns the minimum value in a specified column. It is used to find the lowest value in a dataset.
SELECT MIN(price) FROM product_prices;
5.COUNT: The COUNT function counts the number of rows that meet certain criteria. It can be used to count all rows in a table or to count rows that meet specific conditions.
SELECT COUNT(*) FROM employees;-- Count all employees
SELECT COUNT(*) FROM orders WHERE status = 'Shipped'; -- Count shipped orders
Certainly! Here are some common SQL functions used in queries like SUM, AVERAGE, MAX, MIN, and COUNT, along with examples using a hypothetical table called sales:
Assume the sales table has the following structure:
1.SUM: Calculates the sum of values in a column.
Example: Calculate the total revenue.
SELECT SUM(quantity * price) AS total_revenue FROM sales;
Result:
total_revenue
180.0
2.AVERAGE: Calculates the average of values in a column.
Example: Calculate the average price of products.
SELECT AVG(price) AS avg_price FROM sales;
Result:
avg_price
12.4
MAX: Finds the maximum value in a column.
Example: Find the maximum quantity sold.
SELECT MAX(quantity) AS max_quantity FROM sales;
Result:
max_quantity
6
MIN: Finds the minimum value in a column.
Example: Find the minimum price of products.
SELECT MIN(price) AS min_price FROM sales;
Result:
min_price
10.0
COUNT: Counts the number of rows in a table or the number of non-null values in a column.
Example: Count the number of orders.
SELECT COUNT(*) AS order_count FROM sales
Result:
order_count
5
Example-2
PRODUCT_GOOD
Example: COUNT()
SELECT COUNT(*)
FROM PRODUCT_GOOD;
Output:
10
Example: COUNT with WHERE
SELECT COUNT*)
FROM PRODUCT_GOOD;
WHERE RATE>=200;
Output:
7
Example: COUNT() with DISTINCT
SELECT COUNT(DISTINCT COMPANY)
FROM PRODUCT_GOOD;
Output:
3
Example: COUNT() with GROUP BY
SELECT COMPANY, COUNT(*)
FROM PRODUCT_GOOD
GROUP BY COMPANY;
Output:
ComA 5
ComB 3
ComC 2
Example: COUNT() with HAVING
SELECT COMPANY, COUNT(*)
SELECT COMPANY, COUNT(*)
GROUP BY COMPANY
HAVING COUNT(*)>2;
Output:
ComA 5
ComB 3
Page updated
Google Sites
Report abuse