Structured Query Language (SQL) – Using GROUP BY/ HAVING

Structured Query Language – Using Group By/ Having

The GROUP BY query is typically used with aggregates.

SELECT d.dept_name, d.location, count(e.emp_ID)

FROM department d, employee e

WHERE d.dept_id = e.dept_id

GROUP BY d.dept_name, d.location


Note that d.dept_name and d.location are the non-aggregate fields and count(e.emp_ID) is the aggregate field.

The GROUP BY should include the non-aggregate key.

You can use the HAVING clause to associate a condition with the GROUP BY aggregate value.

E.g.

SELECT d.dept_name, d.location, count(e.emp_ID) as “Total_emp”

FROM department d, employee e

WHERE d.dept_id = e.dept_id

GROUP BY d.dept_name, d.location

HAVING Total_emp > 10

The query will generate a list of the total employees within each department and location having the total > 10

Sample output

Dept_name Location

Total_emp

Sales Bombay

14

Sales Kolkata

17

Research Bombay

27

Marketing Kochi

12

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s