MYSQL SELECT rows where sum is over 200

Question

How to select rows where sum of a row is over 200?

I tried all kinds of combinations with grouping, setting AS something and using WHERE clause

Current attempt as follow

SELECT something.CustomerName, something.CustomerAge, cars.Prices, 
       SUM(cars.Price) AS Amount 
FROM cars 
INNER JOIN something ON something.CustomerNo=Cars.CustomerNo 
GROUP BY AMOUNT 
WHERE AMOUNT > '200' 

I could not find a tutorial on how to do this

Answer 1

According to your current attempt where clause should go before group by clause

SELECT something.CustomerName, something.CustomerAge, 
       SUM(cars.Price) AS Amount 
FROM cars 
INNER JOIN something ON something.CustomerNo=Cars.CustomerNo  
GROUP BY something.CustomerName, something.CustomerAge
HAVING SUM(cars.Price) > 200;

However, you actually need to apply your filter on Amount but, you can't do that via where clause for that you would need to apply having clause filter rather than where clause

My today advice is to use table alise that could be more readable and easy to use/implement

SELECT s.CustomerName, s.CustomerAge,
       SUM(c.Price) AS Amount 
FROM cars as c -- use of alise to make it more effective or readable
INNER JOIN something as s ON s.CustomerNo = c.CustomerNo  -- and use that alise everywhere entire the query 
GROUP BY s.CustomerName, s.CustomerAge
HAVING SUM(c.Price) > 200;

Answer 2

You could use a subquery to do your maths, then select the values you want

SELECT * FROM
(
    SELECT (col1 + col2) AS SumOfCols, table.* FROM table
)
WHERE SumOfCols > 200

Or another similar approach is to join an ad-hoc table

SELECT table.* FROM table
INNER JOIN
(
    SELECT ID, (col1 + col2) AS SumOfCols FROM table
) AS TableSums ON TableSums.ID = table. ID
WHERE TableSums.SumOfCols > 200