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!
yeurlPosted Dec 17, 2011, 6:18 PM
Nice article for beginners, but you could go far in your explains - as example for aliasing table name in join request. Otherwise, you said "The following example shows the list of all Clients name who got the salary more than 126 on average", that wrong - if you look your request, you will have only return for user "Don" or "Darwin", not for all users :). I think you request should be: select name, sum(salary) from clients group by name having sum(salary)>126;