SQL basics : use of group by and having

The purpose of this article is to record my understanding of the basic usage of GROUP BY and HAVING in SQL.

Group by

• Used with aggregate function
• If there are multiple different numbers in a field that need to be calculated, they can be seen as a group
• Use where to filter specific field, and having for specific group(s).
  • having always goes after group by

Example

The total amount of all data for the same order number.

orderId: 1 amount: 8000

orderId: 3 amount: 4000

orderId: 1 amount: 5000

orderId: 1 amount: 4000

Sum the amount of all orders with orderId = 1.

select orderId, sum(orderAmount) as totalAmount from orderList where id = 1 group by orderId;

Having

• Used with aggregate function
• Return the filtered groups

Example

Find all institutions with an average scholarship greater than 7000.

SELECT institutionId, ROUND(avg(scholarshipAmount),5) AS total FROM scholarshiprecord GROUP BY institutionId Having total > 7000

Conclusion

• group by : to filter out multiple different groups with the same field name
  • For example: Sum of order for multiple ordierIds
• having : to be used to filter these groups
  • The specific condition of the groups, For example, the average greater than..., the total greater than ..., etc.