SQL Aggregate Functions
SQL Aggregate Functions
What is SQL Aggregation?
SQL aggregation is a set of values to return a single value.
Aggregate function is available in Excel, SQL, Power BI-Dax.
It's done with the help of aggregate functions, such as SUM, COUNT, and AVG.
Let's look about SQL Aggregate functions today.
For example, in a database of products, we might want to calculate the average price of the whole inventory.
How is it used?
Aggregation in SQL is, typically, used in conjunction with grouping.
The Group By clause is used to arrange rows into groups in SQL.
Aggregation, together with grouping, is key to generating quick reports and insights from a database.
Commonly Used SQL Aggregate Functions
The following are some of the most commonly used SQL aggregate functions:
COUNT: It is used to count the number of rows in a set. the COUNT function includes rows with NULL values.
It is important to note that all aggregate functions ignores NULL values in a set or group, except the COUNT function
Example: --1.count the number of Branches in each city.
select City,
count(branchid) as Numb_of_Branches
from Branches
group by city
order by city;
Output:
--2.write a SQL query to calculate Total Transanction amount of all orders. Return Total Transaction amount.
SUM: It is used to calculate the sum of all non-NULL values in a group.
.
Example:
select Transactionid,sum(amount) as Total_Transactions
from Transactions
group by TransactionID
Output:
AVG: It calculates the average of all values in a group.
Syntax:select CustomerID,Firstname,AVG(Amount) as AVG_Transac_Amount
from Customers as C
join Transactions as T
on c.CustomerID = T.TransactionID
group by CustomerID,Firstname;
Output:
MIN: It returns the lowest value in a group.
Syntax:select AccountID,Firstname,MIN(Balance) as Cust_Min_Balance
from Accounts as A
join Customers as C
on a.AccountID = c.CustomerID
group by AccountID,Firstname;
output:
MAX: It returns the largest value in a group.
Syntax:select TransactionID,Firstname,city,MAX(TransactionID) as MAX_Transaction
from Transactions as T
join Customers as C
on C.CustomerID = T.TransactionID
where City = 'New York'
group by T.TransactionID, C.Firstname, C.city
Output:
Comments
Post a Comment