Group by before Having

In Building and Organizing Complex Queries creating views. why the Group By clause had to be written before the Having clause to prevent an error?
code below

CREATE VIEW chinook.customer_gt_90_dollars AS
    SELECT c.*
    FROM chinook.customer c
    INNER JOIN chinook.invoice i ON i.customer_id = c.customer_id
    GROUP BY c.customer_id
    HAVING SUM(i.total) > 90;
    
    
SELECT *
FROM chinook.customer_gt_90_dollars;

Hey, Curtly.

Both WHERE and HAVING are clauses that allow us to filter (or subset) data based on conditions.

Let’s look at a very similar query to the one you’re asking about:

CREATE VIEW chinook.customer_gt_90_dollars AS 
SELECT c.*, SUM(i.total) as sum_total
  FROM chinook.invoice i
 INNER JOIN chinook.customer c ON i.customer_id = c.customer_id
 GROUP BY 1
HAVING sum_total > 90;

I just included SUM(i.total) in the SELECT statement, named it sum_total and used its alias in the HAVING clause.

Why did I use HAVING instead of WHERE?

The reason is that HAVING is reserved to subset on the direct result of GROUP BY, while WHERE works everywhere else. That’s probably why there’s this specification that HAVING comes after GROUP BY.

I hope this helps.

1 Like