SQL database also provides many other commands that can be added to a SELECT/WHERE query, which in turn, can be used to provide aggregate data. Let’s take of look at some of them one by one.

MAX

WHAT IS THE MAXIMUM INVOICE AMOUNT?
SELECT 
MAX(AMOUNT) 
FROM INVOICES 
MAX(AMOUNT)9876FIGURE 27: The maximum invoice amount MIN
It is but natural that if a MAX function exists, a MIN function should exist as well.

WHAT IS THE MINIMUM INVOICE AMOUNT?
SELECT 
MIN(AMOUNT) 
FROM INVOICES 
MIN(AMOUNT)9876FIGURE 28: The minimum invoice amount Both of these functions are self explanatory. MAX selects the maximum value from any column specified within the brackets, whereas MIN selects the minimum value. Both of these functions can only be applied to numerical and or date/time columns. SUM

WHAT IS THE TOTAL OF ALL INVOICES ISSUED?
SELECT 
SUM(AMOUNT) 
FROM INVOICES 
SUM(AMOUNT)23724FIGURE 28: The sum total of all invoice amounts The SUM function adds up all the values in a numerical column (but naturally, can’t be applied to columns containing text). AVG

WHAT IS THE AVERAGE AMOUNT OF THE INVOICES ISSUED?
SELECT 
AVG(AMOUNT) 
FROM INVOICES 
AVG(AMOUNT)3954FIGURE 28: The average of all invoice amounts The AVERAGE function adds up all the values in a numerical column and then divides the result with the total number of columns (once again, can’t be applied to columns containing text). COUNT

HOW MANY INVOICES ARE ISSUED BY SUPPLIERS TO US?
SELECT 
COUNT(*) 
FROM INVOICES 
SQL Database – COUNT(*)6FIGURE 29: The number of invoices This function warrants some explanation. The COUNT function simply counts the number of rows in the table. Had we written COUNT(INVOICE_NO) or COUNT(AMOUNT), the answer would have still been the same. The “*” simply indicates that the column name is immaterial for the query. In this case the “*” may be read as “any column”. These functions are in themselves not as useful, unless that are used for summary reports. However when these functions are combined with the WHERE clause, new possibilities arise. This can be demonstrated by the examples given below. WHAT IS THE MAXIUM INVOICE AMOUNT ISSUED BY THE SUPPLIER WITH ID 2?
SELECT MAX(AMOUNT) FROM INVOICES  
WHERE SUPPLIER_ID = 1 
MAX(AMOUNT)6FIGURE 30: MAX and WHERE combined Similar remarks apply to the rest of the functions namely MIN, AVG, COUNT, SUM etc. How can you answer the question: WHICH INVOICES EXCEED THE AVERAGE INVOICE AMOUNT?
You can of course order the table in descending order and manually check this by USING the AVG(AMOUNT) query. But this approach is not efficient as the entire table would have to be ordered in that way, thereby wasting processor time and other computer resources.
Another and more efficient way would be to use what in programming jargon is called “Query Nesting”. This will be made clear in the following example.
SELECT * FROM INVOICES  
WHERE AMOUNT >  
(SELECT AVG(AMOUNT) FROM INVOICES); 
INVOICE_NO 
SUPPLIER_ID 
INVOICE_DATE 
AMOUNT 
5 
2 
26Feb2011 
9876 
6 
1 
27Feb2011 
4500 
FIGURE 31: Query nesting result 
Query nesting simply refers to the practice of supplying the output of one query (called “subquery”) to another. The sub query is evaluated first; the result of the subquery is then passed on to the outer query. Hence, in the above example, the average amount was calculated first (FIGURE 28 tells us that this is “3954″). This was then passed to the outer query which then basically read as follows: SELECT * FROM INVOICES WHERE AMOUNT > 3954.

DISTINCT FUNCTION
The DISTINCT function is mostly used in conjunction with the COUNT function. Imagine a scenario, where we want to count the number of suppliers we have, without referring to the “Suppliers” table. If we apply the COUNT(SUPPLIER_ID) function, we get a result as shown in FIGURE 29. To overcome this, DISTINCT function is required.
SELECT  COUNT(DISTINCT(SUPPLIER_ID))  FROM INVOICES; 
COUNT(DISTINCT(SUPPLIER_ID)) 

3 

FIGURE 32: MAX and WHERE combined 
The DISTINCT function ignores duplicate information from a column, so that the COUNT function does not count the duplicate entries. The COUNT(DISTINCT(SUPPLIER_ID)) clasue can also be considered as a nested query.
This basically sums up the discussion on some commonly used functions in SQL database. In the next section we will move into slightly advanced territories and discuss grouping and joining two seperate tables.
guild wars 2 gold on October 11, 2012 at 1:01 am said:
Great post. I was checking constantly this blog and I’m impressed! Extremely helpful info specifically the last part I care for such information a lot. I was looking for this particular information for a long time. Thank you and good luck.
a on June 23, 2014 at 11:22 am said:
Terrific post however I was wanting to know if you could write a litte more on this subject?
I’d be very thankful if you could elaborate
a little bit more. Cheers!