How can I use WHERE clause in this SQL query?-CodePudding

I have two tables - Customers and Orders. Customers just contains an ID and the name. Orders contains an Order ID, a Customer ID, and the amount of the Order. My goal is to sum the orders for each customer,even for customers who didn't have any orders (so, a 0 in the result set).

Problem I actually need to show only the entries for which the total for each customer id < 500. I have a WHERE clause for this, but I get an error.

SELECT * FROM orders;
SELECT * FROM customer;

SELECT
    c.customer_id, c.customer_name, ISNULL(sum(o.order_total),0) as grand_total
FROM
    customer c
LEFT JOIN
    orders o
ON
    c.customer_id = o.customer_id
--WHERE
    --grand_total < 500
GROUP BY
    c.customer_id, c.customer_name
ORDER BY
    grand_total

enter image description here

I was able to do this with a LEFT JOIN,and have the Customers table on the "left", to get every customer, and to show a 0 when they had no orders. I aliased the sums of each order (by customer) as grand_total. When I use grand_total in and ORDER_BY, the alias is recognized, and the grand_total column sorts as expected. But when I reference the grand_total alias in my WHERE statement (i.e. WHERE grand_total < 500), I get the error Invalid column name 'grand_total'

I'm confused as to why grand_total is recognized in the ORDER_BY but not the WHERE. Aside from that, I'm trying to find the right syntax for a WHERE clause to include only the rows for which grand_total < 500.

CodePudding user response：

Use HAVING for filtering groups, not WHERE:

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(sum(o.order_total), 0) as grand_total
FROM customer c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY 1, 2
HAVING COALESCE(sum(o.order_total), 0) < 500
ORDER BY 3

Since you didn't specify which database you're using, I changed ISNULL to COALESCE and used the actual expression in HAVING, which makes it totally portable.

Also made use of column numbers in GROUP BY and ORDER BY rather than repeating expressions to make the query easier on the eyes.