Having and Alias Clause in MySQL

Introduction

In this tutorial, I am going to explain 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>;

The HAVING clause appears and is used after the SELECT, FROM, WHERE, and GROUP BY clauses and before the ORDER BY clause.

MySQL HAVING Clause Examples

Without wasting time, let’s create a database and a table and insert some rows into it.

Create a database

CREATE database HAVING_Clause_db;

Create a table

USE HAVING_Clause_db
CREATE TABLE Library(
BookNumber int NOTNULL,
BookCode varchar(15) NOTNULL,
BookIssue int NOTNULL,
CostEach decimal(10,2) NOTNULL,
PRIMARYKEY (BookNumber, BookCode)
);
 CREATE TABLE Bookorder(
BookNumber int NOTNULL,
orderDatedate NOTNULL,
shippedDatedate DEFAULT NULL,
Status varchar(50),
PRIMARYKEY(BookNumber)
);

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 BYclause is used to get the booknumbers, the number of issued books, and TotalCost from the“Library”table. Let’s see.

Example.

SELECT booknumber,
SUM(bookissue) AS NumberOfIssuedBooks,
SUM(costeach * bookissue) AS TotalCost
FROM library
GROUPBY 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.

SELECT booknumber,
SUM(bookissue) AS NumberOfIssuedBooks,
SUM(costeach * bookissue) AS TotalCost
FROM library
GROUP BY booknumber
HAVING TotalCost>12000;

C) Complex MySQL HAVING Clause Example

In the following example, HAVINGClause is used to retrieve the result having “TotalCost > 10000” and “NumberOfIssuedBooks > 180” from the“Library”table. Let’s see.

Example.

SELECT booknumber,
SUM(bookissue) AS NumberOfIssuedBooks,
SUM( costeach * bookissue) AS TotalCost
FROM library
GROUPBY booknumber
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

SELECT 1 AS VATSA,2 AS RoyalVatsa;

A) To sort the output by ALIAS

Example.

SELECT * FROM having_clause_db.studentdetails;

B) Aliasing the Function in SELECT Statement

Example 1.

SELECT BookNumber,Status,CURDATE(),
(month(curdate())-month(shippedDate)) AS DeliveryPeriod
FROM bookorder
ORDERBY DeliveryPeriod asc;

Example 2.

SELECT StudentNumber,StudentName,concat(addressLine1,",",City)AS StudentFullAddress
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.

SELECT lbr.booknumber,odr.Status,odr.shippedDate,
SUM(bookissue) AS NumberOfIssuedBooks,
SUM(costeach*bookissue) AS TotalCost
FROM library lbr
INNER JOIN bookorder odr
ON lbr.booknumber=odr.booknumber
GROUP BY odr.booknumber
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!