mysql中SUM(IF)条件的故障

Question

有人能帮我修复下面的查询吗？我需要生成一份报告，能够计算出在没有特定时间的情况下发送了多少份TOB &签名。有3种不同的TOB表。下面是我的SQL查询：

select cl.name, con.name,
    COUNT(IF(DATE( tp.sent_on ) between '2012-01-01' AND '2012-12-31', 1, 0))  as "TOB Perm Sent",
    COUNT(IF(DATE( tp.signed_on ) between '2012-01-01' AND '2012-12-31', 1, 0))  as "TOB Perm Signed",
    COUNT(IF(DATE( tt.sent_on ) between '2012-01-01' AND '2012-12-31', 1, 0))  as "TOB Temp Sent",
    COUNT(IF(DATE( tt.signed_on ) between '2012-01-01' AND '2012-12-31', 1, 0))  as "TOB Temp Signed",
    COUNT(IF(DATE( tc.sent_on ) between '2012-01-01' AND '2012-12-31', 1, 0))  as "TOB Contract Sent",
    COUNT(IF(DATE( tc.signed_on ) between '2012-01-01' AND '2012-12-31', 1, 0))  as "TOB Contract Signed"
FROM clients cl
    LEFT OUTER JOIN consultants con ON cl.CIC = con.con_id
    LEFT OUTER JOIN tob_perm tp ON tp.client_id = cl.client_id
    LEFT OUTER JOIN tob_temp tt ON tt.client_id = cl.client_id
    LEFT OUTER JOIN tob_contract tc ON tc.client_id = cl.client_id
WHERE cl.status IN(1) and cl.client_id = 16
GROUP BY cl.client_id
order by cl.client_id asc

样本数据：咨询公司表

id       name
-----------------
 1       Andy

客户端表

id       name       con_id
----------------------------
 1     Client A        1

TOB Perm

id       client_id          sent_on          signed_on
-------------------------------------------------------
 1           1            2012-02-05         0000-00-00
 2           1            2012-10-09         2012-06-03

TOB温度

id       client_id          sent_on          signed_on
-------------------------------------------------------
 1           1            0000-00-00         2012-08-30
 2           1            0000-00-00         2012-02-19
 3           1            2012-10-09         2012-06-03

TOB合同

id       client_id          sent_on          signed_on
-------------------------------------------------------
 1           1            2012-08-30         0000-00-00 

我想要产生的表结果如下：

No    Client Name    Consultant     TOB perm sent     TOB perm signed     TOB temp sent       TOB temp signed      TOB contract sent       TOB contract signed
-----------------------------------------------------------------------------------------------------------------------------------------------------------
1      Client A        Andy              2                   1                  1                     3                    1                       0        

有人能帮我提一下问题吗？提前谢谢。

更新我认为我的IF条件有问题：

IF(DATE( tp.sent_on ) between '2012-01-01' AND '2012-12-31', 1, 0)

有人能帮我解决这个问题吗？谢谢

Answer 1

将count替换为sum以使其工作。

count对记录进行计数，而不关心条件。例如使用

SUM(DATE( tp.sent_on ) between '2012-01-01' AND '2012-12-31')