SQL PHP HTML ASP JavaScript articles and free scripts to download

SQL HAVING command used with GROUP BY Command

Please read basic of group by command Part I

We can use sql having command to add condition to the query. The advantage of using HAVING command is it can be used in aggregate functions like count, max etc. We can't use SQL WHERE clause here. This way we can add value to our select command as per requirement. Usually SQL HAVING command is used in conjunction with group by command. Please read the sql group by command and SQL sub group by command to know more about grouping the records.

Here is the full table with all the rows. On this we will apply the group by query

d_id	name	type	category	price	stock
1	Book1	Book	Management	220	10
2	Book2	CD	Management	120	8
3	Book3	Report	Management	25	4
4	Book4	Book	Management	55	8
5	Document1	Book	Management	15	23
6	Document2	CD	Computers	80	45
7	Document3	Report	Computers	55	65
8	Book5	Report	Management	80	10
9	Document4	CD	Management	72	5
10	Book 8	Book	Computers	88	6
11	Book 9	CD	Computers	100	5
12	Document5	Report	Computers	85	8
13	Book 10	Book	Computers	150	5

Here is the SQL having command with group by command applied on the above table.

SELECT category, type , count( * ) as total FROM `documents` GROUP BY category, type HAVING total < 3

This will display the total records in each group and sub group. Like this

category	type	total
Computers	Book	2
Computers	CD	2
Computers	Report	2
Management	CD	2
Management	Report	2

Related Tutorial

• SQL Group by
• Copy data to new table
• SQL Left Join
• SQL count
• PHP MySQL functions

Without the HAVING command you will get a result records like this below.

SELECT category,type, count(*) as total FROM `documents` group by category,type

This will display the total records in each group and sub group. Like this

category	type	total
Computers	Book	2
Computers	CD	2
Computers	Report	2
Management	Book	3
Management	CD	2
Management	Report	2

Part II Learn the basic of sql group by command

Download the SQL group by query file to create the table with data

Discuss this tutorial at forum

List of SQL Tutorials

Scripts

PHP

JavaScript

HOME

SQL Tutorial List

SQL (Home)

SQL Commands

Subscribe

Submit your email address and receive article and product notifications. Your email is safe with us.