SQL查询未返回预期结果

问题描述：

我编写了以下查询以返回有关在X时间内进行的购买的一些统计信息。但由于某些原因，每个“COUNT”列都会返回总行数。我是否错误地组织了查询？SQL查询未返回预期结果

SELECT COUNT(*) as countTotal, SUM(`cost`) as cost, COUNT(`paymentType` = 'credit') as count_credit, COUNT(`paymentType` = 'cash') as count_cash 
FROM `purchase` WHERE `date` >= '2011-5-4'

更新

我只是决定使用子查询。这是我结束了。

SELECT 
COUNT(*) as countTotal, 
SUM(`cost`) as cost, 
(SELECT COUNT(*) FROM `purchase` WHERE `paymentType` = 'credit') as count_credit, 
(SELECT COUNT(*) FROM `purchase` WHERE `paymentType` = 'cash') as count_cash 
FROM `purchase` WHERE `date` >= '2011-5-4'

UPDATE2

使用ypercubes回答以下。

我可能在这里错了，但我不认为你可以在'COUNT'函数中使用像'paymentType ='credit''这样的表达式。我想你需要使用子查询（或用户定义的函数）来代替。 – 2011-05-05 16:10:11

如果你有100行满足日期标准，其中50个是paymentType ='credit'，你会期望结果'100，X，50'其中'X'是所有100行的总和吗？或者'X'只是信用支付的总和？ – 2011-05-05 16:13:11

如果我总共有100行50信用和50现金全部1美元，我想说的是： 100，100，50，50 – mrkmg 2011-05-05 16:14:15

答

它看起来并不正确的，但改变COUNT()到SUM()正常工作：

SELECT COUNT(*) AS countTotal 
    , SUM(cost) AS cost 
    , SUM(paymentType = 'credit') AS count_credit --- SUM does counting here 
    , SUM(paymentType = 'cash') AS count_cash  --- and here 
FROM purchase 
WHERE `date` >= '2011-05-04'

说明：针对MySQL的True == 1和False == 0。

宾果，这就是我真正想要的。谢谢你太多了。 Man，@ypercube这是你第二次救了我。你是男人。 – mrkmg 2011-05-05 16:23:38

答

count确实返回查询的域或组的行数。看起来你需要通过PaymentType进行分组才能实现你正在寻找的东西。

SELECT PaymentType, COUNT(*) as countTotal, SUM(`cost`) as cost, 
FROM `purchase` 
WHERE `date` >= '2011-5-4' 
Group by PaymentType

这里是一个参考 http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

答

你需要一个GROUP BY子句中的WHERE子句后

SQL查询未返回预期结果

相关推荐