Prev Next

MySQL HAVING

The HAVING clause is used to filter groups of records created by the GROUP BY clause. It is similar to the WHERE clause, but is used for aggregate functions.

Examples with Tamil Kings

1. Using HAVING with COUNT

SELECT reign_period, COUNT(*) AS king_count FROM tamil_kings_auto_increment
GROUP BY reign_period
HAVING COUNT(*) > 1;

Code Explanation: This query groups records by reign_period and selects only those groups that have more than one king.

2. Using HAVING with SUM

SELECT kingdom, SUM(reign_years) AS total_reign_years FROM tamil_kings_auto_increment
GROUP BY kingdom
HAVING SUM(reign_years) > 50;

Code Explanation: This query groups records by kingdom and selects only those groups where the total reign years exceed 50.

Best Practices

  • Use HAVING to filter groups of records based on aggregate functions.
  • Combine WHERE and HAVING for more precise filtering, using WHERE to filter rows before grouping and HAVING to filter groups.

Key Takeaways

  • The HAVING clause filters groups created by GROUP BY.
  • It is often used with aggregate functions like COUNT, SUM, and AVG.
Prev Next