这是我的代码:
SELECT CASHIER_ID AS SERVER, CONVERT(VARCHAR(10),[DATETIME],111) AS DATE,
SUM(GRAND_TOTAL) AS TOTAL_SALES,
SUM(NUM_PEOPLE_PARTY) AS NUMBER_SERVED,
SUM(GRAND_TOTAL) / SUM(NUM_PEOPLE_PARTY) AS CASHPERCUSTOMER
FROM INVOICE_TOTALS
WHERE [DATETIME] >= '2012-06-01'
GROUP BY CASHIER_ID, CONVERT(VARCHAR(10),[DATETIME],111)
一切都与代码完美配合,除了:
SUM(GRAND_TOTAL) / SUM(NUM_PEOPLE_PARTY) AS CASHPERCUSTOMER
部分。如果我把这条线拿出来,那么一切都会完美,如果我把它放进去,我会收到以下错误:
消息:无法检索数据表。
SELECT CASHIER_ID AS SERVER,...上面查询的其余部分...
堆栈跟踪:
...不相关的堆栈跟踪...
信息:遇到除零错误。
堆栈跟踪:
...不相关的堆栈跟踪...
这是使用 SQL Server 的餐厅销售点 (POS) 系统内的 SQL。有什么想法可能会发生这种情况吗?
除法前检查是否为 0:
SELECT
...,
CASE WHEN SUM(NUM_PEOPLE_PARTY) = 0
THEN NULL
ELSE SUM(GRAND_TOTAL) / SUM(NUM_PEOPLE_PARTY) END AS CASHPERCUSTOMER,
...
或者通过使用获得相同的结果NULLIF http://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html#function_nullif:
SELECT
...,
CASE SUM(GRAND_TOTAL) / NULLIF(SUM(NUM_PEOPLE_PARTY), 0) AS CASHPERCUSTOMER,
...
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)