mysql - SQL Where SUM() 在两个值之间

Question

我正在尝试查找 available_balance 之和介于两个值之间的帐户信息。我尝试了以下操作，但它没有按预期工作:

SELECT sum(a.avail_balance) `sum`
FROM account a
WHERE  `sum` BETWEEN 5000 AND 10000

结果:错误 1054 (42S22):“where 子句”中的未知列“sum”

我怎样才能达到预期的结果？

我的 table :

+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+
| account_id | product_cd | cust_id | open_date  | close_date | last_activity_date | status | open_branch_id | open_emp_id | avail_balance | pending_balance |
+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+
|          1 | CHK        |       1 | 2000-01-15 | NULL       | 2005-01-04         | ACTIVE |              2 |          10 |       1057.75 |         1057.75 |
|          2 | SAV        |       1 | 2000-01-15 | NULL       | 2004-12-19         | ACTIVE |              2 |          10 |        500.00 |          500.00 |
|          3 | CD         |       1 | 2004-06-30 | NULL       | 2004-06-30         | ACTIVE |              2 |          10 |       3000.00 |         3000.00 |
|          4 | CHK        |       2 | 2001-03-12 | NULL       | 2004-12-27         | ACTIVE |              2 |          10 |       2258.02 |         2258.02 |
|          5 | SAV        |       2 | 2001-03-12 | NULL       | 2004-12-11         | ACTIVE |              2 |          10 |        200.00 |          200.00 |
|          7 | CHK        |       3 | 2002-11-23 | NULL       | 2004-11-30         | ACTIVE |              3 |          13 |       1057.75 |         1057.75 |
|          8 | MM         |       3 | 2002-12-15 | NULL       | 2004-12-05         | ACTIVE |              3 |          13 |       2212.50 |         2212.50 |
|         10 | CHK        |       4 | 2003-09-12 | NULL       | 2005-01-03         | ACTIVE |              1 |           1 |        534.12 |          534.12 |
|         11 | SAV        |       4 | 2000-01-15 | NULL       | 2004-10-24         | ACTIVE |              1 |           1 |        767.77 |          767.77 |
|         12 | MM         |       4 | 2004-09-30 | NULL       | 2004-11-11         | ACTIVE |              1 |           1 |       5487.09 |         5487.09 |
|         13 | CHK        |       5 | 2004-01-27 | NULL       | 2005-01-05         | ACTIVE |              4 |          16 |       2237.97 |         2897.97 |
|         14 | CHK        |       6 | 2002-08-24 | NULL       | 2004-11-29         | ACTIVE |              1 |           1 |        122.37 |          122.37 |
|         15 | CD         |       6 | 2004-12-28 | NULL       | 2004-12-28         | ACTIVE |              1 |           1 |      10000.00 |        10000.00 |
|         17 | CD         |       7 | 2004-01-12 | NULL       | 2004-01-12         | ACTIVE |              2 |          10 |       5000.00 |         5000.00 |
|         18 | CHK        |       8 | 2001-05-23 | NULL       | 2005-01-03         | ACTIVE |              4 |          16 |       3487.19 |         3487.19 |
|         19 | SAV        |       8 | 2001-05-23 | NULL       | 2004-10-12         | ACTIVE |              4 |          16 |        387.99 |          387.99 |
|         21 | CHK        |       9 | 2003-07-30 | NULL       | 2004-12-15         | ACTIVE |              1 |           1 |        125.67 |          125.67 |
|         22 | MM         |       9 | 2004-10-28 | NULL       | 2004-10-28         | ACTIVE |              1 |           1 |       9345.55 |         9845.55 |
|         23 | CD         |       9 | 2004-06-30 | NULL       | 2004-06-30         | ACTIVE |              1 |           1 |       1500.00 |         1500.00 |
|         24 | CHK        |      10 | 2002-09-30 | NULL       | 2004-12-15         | ACTIVE |              4 |          16 |      23575.12 |        23575.12 |
|         25 | BUS        |      10 | 2002-10-01 | NULL       | 2004-08-28         | ACTIVE |              4 |          16 |          0.00 |            0.00 |
|         27 | BUS        |      11 | 2004-03-22 | NULL       | 2004-11-14         | ACTIVE |              2 |          10 |       9345.55 |         9345.55 |
|         28 | CHK        |      12 | 2003-07-30 | NULL       | 2004-12-15         | ACTIVE |              4 |          16 |      38552.05 |        38552.05 |
|         29 | SBL        |      13 | 2004-02-22 | NULL       | 2004-12-17         | ACTIVE |              3 |          13 |      50000.00 |        50000.00 |
+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+

Answer 1

只能在HAVING子句中使用聚合进行比较:

GROUP BY ...
  HAVING SUM(avail_balance) BETWEEN 5000 AND 10000

HAVING 子句要求您定义一个GROUP BY 子句。

假设，你想SUM每个cust_id的余额，你需要像

SELECT sum(a.avail_balance) `sum`
FROM account a
GROUP BY `cust_id` 
HAVING SUM(a.avail_balance) BETWEEN 5000 AND 10000