MySQL Server : Order By/Group By/Aggregate functions

• MySQL Order By
• MySQL Aggregate Functions
• MySQL Group By
• Downloads Examples

The order by clause is used to do sorting on the query result sets in either ascending or descending order. It is used in conjunction with the SELECT query.

– The ORDER BY clause of the SELECT statement is used to sort records.
– The ORDER BY clause determines the order in which rows are returned in a results set.

Syntax : in short

ORDER BY <order by definition> <order by definition>::=

<column name> [ASC | DESC][{, <column name> [ASC | DESC]}...] 

Order By syntax

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Note: The ORDER BY clause must include the ORDER BY keywords and at least one column name or a column alias. If you include more than one column, a comma must separate them.
Order By Example

SELECT customer_id, store_id, first_name, last_name 
FROM customer 
ORDER BY last_name; 
-- Sorting by a single column last_name 

SELECT customer_id, store_id, first_name, last_name
FROM customer
ORDER BY store_id, last_name; 
-- Nested sorting By Multiple Columns.

– By default, for each column in an ORDER BY clause, records are sorted in ascending order. This can be explicitly specified with the ASC keyword. To sort records in descending order, use the DESC keyword after the column name.
– In addition, when more than one column is specified, the rows are sorted first by the column that is listed first, then by the next specified column, and so on.

SELECT film_id, title, rating, rental_rate
FROM film
ORDER BY 4 ASC, 3 DESC; 
-- Sorting By Column Position (numeric) instead of column name.

SELECT title, rating, rental_rate
FROM film
ORDER BY rental_rate ASC, rating DESC, title ASC; 
-- Specify and mix Ascending and Descending Sorts.

-- In this case, the returned rows are sorted first by 
-- the values in the rental_rate column in ascending order) and 
-- then by the values in the rating column in descending order and so on.

The LIMIT Clause

• The LIMIT clause is used most effectively in a SELECT statement when it is used with an ORDER BY clause.
• The LIMIT clause takes two arguments,

Syntax:

LIMIT [<offset>,] <row count> 

– The first option, , is optional and indicates where to begin the LIMIT row count.
– If no value is specified, 0 is assumed. (The first row in a result set is considered to be 0, rather than 1.) The second argument, in the LIMIT clause, indicates the number of rows to be returned.

Example:

SELECT last_name, first_name, email
FROM customer LIMIT 5; 
Code Explanation
-- Select any first five customers.

SELECT title, rating, rental_rate
FROM filmTab
ORDER BY rating DESC, title ASC LIMIT 5;

SELECT title, rating, rental_rate
FROM filmTab
ORDER BY rental_rate ASC, rating DESC, 
title ASC LIMIT 2, 4; 

The example includes a LIMIT clause that specifies an offset value of 2 and a row count value of 3. So three rows starting from the third row are returned.

One key feature supported by the SELECT is to find aggregate values, grouping data elements as needed using aggregate functions.

Aggregate functions are used to calculate results using field values from multiple records. There are five common aggregate functions.

Example:

SELECT customer_id, MAX(rental_date) 'LastRentalOn‘
FROM rentalTab GROUP BY customer_id;

SELECT customer_id, MIN(payment_date) 'OldestPaymentOn‘
FROM paymentTab GROUP BY customer_id;

SELECT customer_id, SUM(amount) 'PaidAmount‘ 
FROM paymentTab GROUP BY customer_id;

SELECT l.name, COUNT(f.film_id) 'FilmCount‘
FROM languageTab l LEFT JOIN film_detail_Tab f 
USING (language_id) GROUP BY f.language_id;

SELECT category_name, ROUND(AVG(length)/60,2) 'Average Length In Hours‘ 
FROM film_detail_Tab GROUP BY category_id; 

Group by clause breaks the table into multiple groups and perform aggregation on the groups

Group By syntax

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Group By Example

SELECT customer_id, MAX(rental_date) 'LastRentalOn‘
FROM rentalTab GROUP BY customer_id;

SELECT customer_id, MIN(payment_date) 'OldestPaymentOn‘
FROM paymentTab GROUP BY customer_id;

SELECT customer_id, SUM(amount) 'PaidAmount‘ 
FROM paymentTab GROUP BY customer_id;

SELECT l.name, COUNT(f.film_id) 'FilmCount‘
FROM languageTab l LEFT JOIN film_detail_Tab f 
USING (language_id) GROUP BY f.language_id;

SELECT category_name, ROUND(AVG(length)/60,2) 'Average Length In Hours‘ 
FROM film_detail_Tab GROUP BY category_id; 

Filtering Aggregates using HAVING Clause

The HAVING clause is used to filter grouped data.
For example, the following code specifies that we only want information on languages that have more than a certain number of films.

Example:

SELECT l.name, COUNT(f.film_id)
FROM language l JOIN film_detail_Tab f USING (language_id)
GROUP BY f.language_id
HAVING COUNT(f.film_id) > 5 ORDER BY 2 DESC; 

SELECT category_name, ROUND(AVG(rental_rate),2) 'Average Rental', 
ROUND(AVG(length),0) 'Average Length‘
FROM film_detail_Tab
GROUP BY category_name
HAVING AVG(length) > 120 ORDER BY category_name;