SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL

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
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

Google+

ajay sarwai09-04-2011
In mysql query SELECT category, type , count( * ) as total FROM `documents` GROUP BY category, type HAVING total < 3
is running perfectly.
Rajesh19-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.
Subhendu19-11-2011
You can use inner join in this case to link same table.
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer