DEV Community

loading...
Cover image for SQL Review - GROUP BY Query

SQL Review - GROUP BY Query

jo_josephs profile image Jo ・2 min read

The GROUP BY clause is mostly used with aggregate functions in sql. Aggregate functions in sql are different from single row functions in one main way: Single row functions return a single result row for EVERY row of a queried table or view. Aggregate functions performs a calculation on a set of values in a column and returns one single value. The 5 most common aggregate functions in sql are: COUNT(), MAX(), MIN(), AVG(),SUM(). These do exactly what they sound like they do, for example:

select max(supply) from fruit_imports;
Enter fullscreen mode Exit fullscreen mode

The other main difference between aggregate functions and single row functions is that while you use the "WHERE" clause to filter information in a single row function, you would use the "HAVING" clause to filter information in an aggregate function query (examples and screenshots coming up, don't worry)

In the graphic above, we see the anatomy of a GROUP BY query.

We will be working with the example below to discuss the GROUP BY query.

If you are using a GROUP BY to group by a particular column, you must specify that column in the GROUP BY clause. Any non-aggregate (not a MIN,MAX,COUNT,AVERAGE or SUM) column must be mentioned in the GROUP BY clause. Please see the screenshot below:

In the screenshot above, there are two columns, the sum_supply column and the state column. The sum_supply column is the aggregate column and the state is the non-aggregate column. The state column is the one that is included in the GROUP BY clause because it is non-aggregated column.

HAVING

Having is used to filter group data. If you are filtering data with the HAVING clause, it must come after the GROUP BY clause. HAVING is used on aggregate functions in lieu of the WHERE clause.

SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
HAVING COUNT(*)>2;
Enter fullscreen mode Exit fullscreen mode

The results in the screenshot show all the employee names that are mentioned more than 2 times. That means in the full list of employee first names, there are 3 Roslyns and 3 Billies.

Thanks for reading! Hope this informative. Please feel free to comment, clarify or add to this :)

Discussion (3)

pic
Editor guide
Collapse
darkain profile image
Vincent Milum Jr

"HAVING is used on aggregate functions in lieu of the WHERE clause."

Not quite. You can have both HAVING and WHERE in the same SQL query, or either of the two for an aggregate query. The difference is that WHERE is applied before the aggregation, and HAVING is applied after.

Collapse
jo_josephs profile image
Jo Author

Thanks for clarifying!

Collapse
kaykaysea profile image
Krishna Karri

Useful article. Thanks!