SQL Reports

All Things Being SQL – SQL Reports and SQL Reporting Resources

SELECTING CONDITIONALLY PART – 4 SQL DATABASE

SQL Database

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.

  1. MAX
  2. 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.

  1. 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

  1. 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

  1. 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

  1. 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

26-Feb-2011

9876

6

1

27-Feb-2011

4500

FIGURE 31: Query nesting result

Query nesting simply refers to the practice of supplying the output of one query (called “sub-query”) to another. The sub query is evaluated first; the result of the sub-query 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.

  1. 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.

 

 

2 comments

  • 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.

  • 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!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>