SQL- AGGREGATE/MULTIPLE ROW FUNCTION

In computer science, an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning

Five Important aggregate functions are COUNT, SUM, AVG, MIN, and MAX. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows.

Aggregate functions and NULL values:
None of the aggregate functions takes NULL into consideration. NULL is simply ignored by
all the aggregate functions

1. COUNT (*) gives the number of rows satisfying the conditions

COUNT() function is used to count the number of values in a column. COUNT() takes one
argument which can be any column name, an expression based on a column, or an asterisk
(*). When the argument is a column name or an expression based on a column, COUNT()
returns the number of non-NULL values in that column. If the argument is a *, then
COUNT() counts the total number of rows satisfying the condition, if any, in the table.
e.g.,

To count the total number of records in the table Shoes.
SELECT COUNT(*) FROM
shoes;
+----------+
| COUNT(*) |
+----------+
| 13 |
+----------+
To count the different types of shoes that the factory produces
SELECT COUNT(distinct type)
FROM shoes;
+----------------------+
| COUNT(distinct type) |
+----------------------+
| 3 |
+----------------------+
'
To count the records for which the margin is greater than 2.00

SELECT COUNT(margin)
FROM shoes
WHERE margin > 2;
+---------------+
| COUNT(margin) |
+---------------+
| 5 |
+---------------+

To count the number of customers in 'A' category

SELECT COUNT(*)
FROM customers
WHERE category ='A';
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
To count the number of orders of quantity more than 300

SELECT COUNT(*)
FROM orders
WHERE order_qty >
300;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+

2. SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric.
3. AVG () gives the average of the given column.
4. MIN () gives the smallest figure in the given column.
5. MAX () gives the largest figure in the given column.

 WidgetOrders

OrderID	FirstName	LastName	Quantity	UnitPrice	Continent
122	John	Jacob	21	4.52	North America
923	Ralph	Wiggum	192	3.99	North America
238	Ryan	Johnson	87	4.49	Africa
829	Mary	Smith	842	2.99	North America
824	Elizabeth	Marks	48	3.48	Africa
753	James	Linea	9	7.85	North America
942	Alan	Jonas	638	3.29	Europe

Let's begin by taking a look at the SUM function. It is used within a SELECT statement and, predictably, returns the summation of a series of values. If the widget project manager wanted to know the total number of widgets sold to date, we could use the following query:

SELECT SUM(Quantity) AS Total FROM WidgetOrders;

Our results would appear as:
Total
-----------
1837

The AVG (average) function works in a similar manner to provide the mathematical average of a series of values. Let's try a slightly more complicated task this time. We'd like to find out the average dollar amount of all orders placed on the North American continent. Note that we'll have to multiply the Quantity column by the UnitPrice column to compute the dollar amount of each order. Here's what our query will look like:

SELECT AVG(UnitPrice * Quantity) As AveragePrice
FROM WidgetOrders WHERE Continent = "North America";

AveragePrice
---------------------
862.3075

part 2: Counting Records
SQL provides the COUNT function to retrieve the number of records in a table that meet given criteria.  We can use the COUNT(*) syntax alone to retrieve the number of rows in a table.  Alternatively, a WHERE clause can be included to restrict the counting to specific records. For example, suppose our Widgets product manager would like to know how many orders our company processed that requested over 100 widgets. SELECT COUNT(*) AS 'Number of Large Orders' FROM WidgetOrders WHERE Quantity > 100; And the results: Number of Large Orders ---------------------- 3 The COUNT function also allows for the use of the DISTINCT keyword and an expression to count the number of times a unique value for the expression appears in the target data.  Similarly, the ALL keyword returns the total number of times the expression is satisfied, without worrying about unique values.  For example, our product manager would like a simple query that returned the number of unique continents in our orders database. First, let's take a look at the use of the ALL keyword: SELECT COUNT(ALL Continent) As 'Number of Continents' FROM WidgetOrders And the result set: Number of Continents -------------------- 7 Obviously, this is not the desired results.  All of our orders came from North America, Africa and Europe.  Let's try the DISTINCT keyword instead: SELECT COUNT(DISTINCT Continent) As 'Number of Continents' FROM WidgetOrders And the output: Number of Continents -------------------- 3 That's more like it!

Aggregate Functions in SQL
Part 3: Max and Min
In this final segment of our aggregate functions feature article, we'll look at the functionality SQL provides to locate the records containing the smallest and largest values for a given expression. The MAX() function returns the largest value in a given data series.  We can provide the function with a field name to return the largest value for a given field in a table.  MAX() can also be used with expressions and GROUP BY clauses for enhanced functionality. Once again, we'll use the  WidgetOrders example table for this query  Suppose our product manager wanted to find the order in our database that produced the most revenue for the company.  We could use the following query to find the order with the largest total dollar value: SELECT MAX(Quantity * UnitPrice)As 'Largest Order' FROM WidgetOrders Our results would look like this: Largest Order --------------------- 2517.58 The MIN() function functions in the same manner, but returns the minimum value for the expression. SELECT Continent, MIN(Quantity * UnitPrice) AS 'Smallest Order' FROM WidgetOrders WHERE Continent ='North America'; And our result set: Continent     Smallest Order ------------- --------------------- North America 70.65