use mysql SUM() in a WHERE clause

by

You can only use aggregates for comparison in the HAVING clause:

GROUP BY ...
  HAVING SUM(cash) > 500

The HAVING clause requires you to define a GROUP BY clause.

To get the first row where the sum of all the previous cash is greater than a certain value, use:

SELECT y.id, y.cash
  FROM (SELECT t.id,
               t.cash,
               (SELECT SUM(x.cash)
                  FROM TABLE x
                 WHERE x.id <= t.id) AS running_total
         FROM TABLE t
     ORDER BY t.id) y
 WHERE y.running_total > 500
ORDER BY y.id
   LIMIT 1

Because the aggregate function occurs in a subquery, the column alias for it can be referenced in the WHERE clause.

More Related Contents:

  1. SQL select only rows with max value on a column [duplicate]
  2. SELECTING with multiple WHERE conditions on same column
  3. Can you use an alias in the WHERE clause in mysql?
  4. WHERE vs HAVING
  5. MySQL “Group By” and “Order By”
  6. Is there ANY_VALUE capability for mysql 5.6?
  7. Aggregate function in MySQL – list (like LISTAGG in Oracle)
  8. SQL Query to get column values that correspond with MAX value of another column?
  9. How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?
  10. MySQL dynamic cross tab
  11. How to select data where a field has a min value in MySQL?
  12. mysql count group by having
  13. i have an error which i dont understand. can you help me? [closed]
  14. mysql error 1364 Field doesn’t have a default values
  15. SQL: What is the default Order By of queries?
  16. How to group by week in MySQL?
  17. Can I use a function for a default value in MySql?
  18. Mysql order by specific ID values
  19. How do I check if a column is empty or null in MySQL?
  20. MySQL versus PDO [duplicate]
  21. Row number per group in mysql
  22. Resetting ROOT password in MySQL 5.6
  23. How to get the mysql table columns data type?
  24. Escaping the colon character ‘:’ in JPA queries
  25. How to export / dump a MySql table into a text file including the field names (aka headers or column names)
  26. WooCommerce: Finding the products in database
  27. How can I “select *” from a table in MySQL but omit certain columns?
  28. For homebrew mysql installs, where’s my.cnf?
  29. MySQL Select Multiple VALUES
  30. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

Leave a Comment