Database Related

In SQL, What’s The Difference Between The Having Clause And The Group By Statement?

In structured query language(SQL) to group the columns of a table in addition to perform some aggregate functions like sum, difference, minimum, maximum etc, and when we want to compare the aggregate functions having clause is used with group by statement. Read on the example below to understand it better.

Example:

We have a table of sales item which contains items with their selling price

Sales Price($)
Item 1 2500
Item2 150
Item3 39803
Item 4 50
Item 5 90

 

In the above table there are 2 columns of sale and price. Now if we want to compute the total Price that each item owned, SQL statement will be written like this:

Select Sales, sum(price) from sales_item group by Sales

Now we want to find out that which sale item has more than 2000$ sale price and we need to find the price greater than 2000$ per item and for this we will use having clause in our Query.

Select sales, sum(price) from sales_item

Group by sales having sum(price) > 2000;

After running this query we just get those records that have prices which are greater than 2000

Sales Price($)
Item 1 2500
Item3 39803

 

Thus, having clause and group by statements are not surely substitutes to each other – but they use one another combined. In short to limit our query we use having clause and to perform aggregate function we use group by statement.

The Difference Between The Having Clause And The Group By Statement

So, as we have elaborated the example above, you can see that the Having clause is used for grouping the column(s) so that the aggregation (such as MAX, SUM) can be used for finding the relevant information. Having clause is also used along with group by clause when such comparisons have to be made with the functions related to aggregation, to check if the SUM is higher. So, it is clear that the group by statements and having clause are not completely alternatives to each other and can be used along with one another.

Leave a Reply