SQL having with group by in a query for MySQL tables

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

Using WHERE condition

We can’t use SQL WHERE condition to get similar result.

This query will generate error message.

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

Use HAVING in place of WHERE in above query.

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

Download the SQL group by query file to create the table with data
SQL Reference Part II Learn the basic of sql group by command

Subscribe to our YouTube Channel here

ajay sarwai	09-04-2011
In mysql query SELECT category, type , count( * ) as total FROM `documents` GROUP BY category, type HAVING total < 3 is running perfectly.

Rajesh	19-10-2011
EMp_ ID EMP Name Manga_ID 1 A 2 B 1 3 C 1 4 D 3 5 E 2 O/p: Emp_name ManagerName A B A C A D C E B What will be the query to get this output.

smo1234	19-11-2011
You can use inner join in this case to link same table.

SQL HAVING command used with GROUP BY Command

Using WHERE condition

Subscribe