Advanced SQL SELECT Tutorial using Aggegrate Functions by Examples

[info]In case you are beginner with SQL, you are advised to visit the following tutorials:
>SQL SELECT Tutorial : Searching & Querying the database
>Join : SELECT Tutorial using multiple database tables[/info]

The important SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table:

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY column_name]
[HAVING clause]
[ORDER BY column_name DESC];

Database Tables Structure

In order to demonstrate the usage of SELECT statement, we will be using the following database during the course of this tutorial . The database is for an ecommerce store with 5 tables as :
Countries, Orders, Clients, Products, Prodcat

advanced-sql

SQL Aggregate Functions

The usual SQL functions that are mostly used are listed below:

  • AVG( ) – Returns the average value
  • COUNT( ) – Returns the number of rows
  • SUM( ) – Returns the sum
  • FIRST( ) – Returns the first value
  • LAST( ) – Returns the last value
  • MAX( ) – Returns the largest value
  • MIN( ) – Returns the smallest value

[success]Note that aggregate functions are used most of the time with GROUP BY[/success]

1 Find the total number of customers ?

2 Find the product having the largest price ?

3 Find the total revenue for the month of June 2010 ?

4 Find the total revenue for the months of Junes ( ALL Years ) ?

5 Find the three most ordered products ?

6 Find the client having paid the largest amount ?

7 Find the first five countries with the largest number of orders ?

8 Find the most popular product in Canada for the year 2012 ?

No Responses

Leave a Reply

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