myITSchools

Aggregate functions in SQL Server

SQL aggregate functions are special functions that operate on a set of values and return a single value as a result. They are often used with the GROUP BY clause to summarize data by groups. Here are some examples of SQL aggregate functions:

  • COUNT: This function returns the number of rows in a table or a group. For example, SELECT COUNT (*) FROM Student returns the total number of students in the table.
  • SUM: This function returns the sum of all values in a column or a group. For example, SELECT SUM(Price) FROM sales returns the total price of all sales.
  • AVG: This function returns the average of all values in a column or a group. For example, SELECT AVG (salary) FROM employees returns the average salary of all employees.
  • MIN: This function returns the minimum value in a column or a group. For example, SELECT MIN (age) FROM customers returns the youngest customer’s age.
  • MAX: This function returns the maximum value in a column or a group. For example, SELECT MAX (score) FROM exams returns the highest exam score.

Suppose you have a table named orders with columns order_id, customer_id, order_value, and payment_date. You want to find the total value of orders placed by customers who have placed more than 10 orders. Here’s how you can do it:

SELECT customer_id, SUM (order_value) AS total_order_value
FROM orders
GROUP BY customer_id
HAVING COUNT (*) > 10;

What is the difference between COUNT (*) and COUNT (column)?

COUNT (*) and COUNT (column) are both SQL aggregate functions used to count the number of rows in a table. However, there is a difference between them.

COUNT (*) counts all rows in a table, including those with NULL values. COUNT (column) counts only the rows where the specified column has a non-NULL value.

For example, if you have a table named orders with columns order_id, customer_id, order_value, and payment_date, you can use COUNT (*) to count the total number of orders, including those with NULL values, like this:

SELECT COUNT (*) AS total_orders
FROM orders;

You can use COUNT (column) to count the number of orders where the customer_id column has a non-NULL value, like this:

SELECT COUNT (customer_id) AS non_null_customer_id_orders
FROM orders;

In this query, we first group the orders by customer using the GROUP BY clause. Then we use the SUM aggregate function to calculate the total order value for each customer. Finally, we use the HAVING clause to filter the results to only include customers who have placed more than 10 orders.

Scroll to Top