Post
Ask Question

Having and Alias Clause in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about MySQL HAVING and Alias Clauses with examples.
 

HAVING CLAUSE

 
We know that the WHERE clause is used to restrict the records in a query. But if we want to restrict the records by using the Aggregate function then we have to use the HAVING clause. The HAVING clause restricts the records after they have been grouped.
 
HAVING Clause id generally is used with the “GROUP BY” clause to filter out group-based data.
 
Syntax
SELECT <column_list>
FROM <table_name>
WHERE [condition(s)]
GROUP BY <groupby_expression>
HAVING <condition>;
 
HAVING clause appears and is used after the SELECT, FROM, WHERE and GROUP BY clauses and before ORDER BY clause.
 
mysql1
 

MySQL HAVING Clause Examples

 
Without wasting time, let’s create a database and a table and insert some rows into it.
 
Create a database
  1. CREATE database HAVING_Clause_db; 
Create a table
  1. USE HAVING_Clause_db  
  2.    
  3. CREATE TABLE Library (  
  4.   BookNumber int NOT NULL,  
  5.   BookCode varchar(15) NOT NULL,  
  6.   BookIssue int NOT NULL,  
  7.   CostEach decimal(10,2) NOT NULL,  
  8.   PRIMARY KEY (BookNumber,BookCode)  
  9. );  
  10.    
  11. CREATE TABLE Bookorder (  
  12.   BookNumber int NOT NULL,  
  13.   orderDate date NOT NULL,  
  14.   shippedDate date DEFAULT NULL,  
  15.   Status varchar(50),  
  16.   PRIMARY KEY (BookNumber)  
  17. ); 
Here, I am also providing you the database with tables containing the records on which I am showing you the various examples.
 

A) Simple MySQL GROUP BY Clause Example

 
In the following example, GROUP BY clause is used to get the booknumbers, the number of issued books, and TotalCost from the “Library” table. Let’s see.
 
Example
  1. SELECT booknumber,  
  2.     SUM(bookissue) AS NumberOfIssuedBooks,  
  3.     SUM(costeach*bookissue) AS TotalCost  
  4. FROM library  
  5. GROUP BY booknumber; 
 

B) Simple MySQL HAVING Clause Example

 
In the following example, HAVING clause is used to retrieve the TotalCost > 10000 from the “Library” table. Let’s see.
 
Example
  1. SELECT booknumber,  
  2.     SUM(bookissue) AS NumberOfIssuedBooks,  
  3.     SUM(costeach*bookissue) AS TotalCost  
  4. FROM library  
  5. GROUP BY booknumber  
  6. HAVING TotalCost > 12000; 
 

C) Complex MySQL HAVING Clause Example

 
In the following example, HAVING Clause is used to retrieve the result having “TotalCost > 10000” and “NumberOfIssuedBooks > 180” from the “Library” table. Let’s see.
 
Example
  1. SELECT booknumber,  
  2.     SUM(bookissue) AS NumberOfIssuedBooks,  
  3.     SUM(costeach*bookissue) AS TotalCost  
  4. FROM library  
  5. GROUP BY booknumber  
  6. HAVING TotalCost > 12000 AND NumberOfIssuedBooks > 180; 
 

ALIAS CLAUSE

 
An alias is a good thing to do if we have very long or complex table names or column names. An alias is created using the term AS followed by a case-sensitive string. Alias can be used in a query select list to give a column a different name in the returned results. You can give a table or a column another name by using an alias. An alias name could be anything, but usually it is short.
 
Example
 
In SELECT query, output column names, by default, are the same as the column or expression selected. To rename a column, provide an alias following the column in the output list
  1. SELECT 1 AS VATSA, 2 AS RoyalVatsa; 
 

A) To sort the output by Alias

 
Example
  1. SELECT * FROM having_clause_db.studentdetails; 
 

B) Aliasing the Function in SELECT Statement

 
Example 1
  1. SELECT BookNumber, Status, CURDATE(),  
  2. (month(curdate())-month(shippedDate)) AS DeliveryPeriod  
  3. FROM bookorder  
  4. ORDER BY DeliveryPeriod asc; 
 
Example 2
  1. SELECT StudentNumber, StudentName, concat(addressLine1, ", " , City) AS StudentFullAddress  
  2. FROM having_clause_db.studentdetails; 
 

C) Complex MySQL HAVING Clause with Alias

 
When we have to join the tables, it's often the case that the tables contain columns with the same names. If we refer to such a column in the query, it's doubtful which table the column reference applies to. This uncertainly usually can be addressed by qualifying column names with table names. However, if we join a table to it, even the table name is uncertain and it's necessary to use aliases to authorize table references.
  1. SELECT lbr.booknumber, odr.Status, odr.shippedDate,     
  2. SUM(bookissue) AS NumberOfIssuedBooks,  
  3. SUM(costeach*bookissue) AS TotalCost  
  4. FROM library lbr  
  5. INNER JOIN bookorder odr  
  6. ON lbr.booknumber = odr.booknumber  
  7. GROUP BY odr.booknumber  
  8. HAVING odr.status = 'Done' AND TotalCost > 5000; 
 

CONCLUSION

 
In this article, I have discussed the concept of HAVING and Alias Clauses in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article!

Similar Articles