Both WHERE and HAVING seem to filter rows in SQL queries. In my practical exam I lost marks for using WHERE with GROUP BY. When should I use each one?
The key difference is timing. WHERE filters individual rows before any grouping happens, and it cannot use aggregate functions like COUNT or SUM. HAVING filters after rows are grouped by GROUP BY, and it is the only place you can use aggregates as a condition. Example: 'SELECT dept, COUNT(*) FROM emp WHERE salary > 10000 GROUP BY dept HAVING COUNT(*) > 5'. Here WHERE removes low-salary rows first, then groups by department, then HAVING keeps only departments with more than five such employees. So use WHERE for conditions on plain columns and HAVING for conditions on grouped results like COUNT(*) > 5. Mixing them up, like putting an aggregate in WHERE, causes an error, which is likely why you lost marks.
Sign in as a tutor to answer this doubt.