按照某一列统计数据

按照某一列统计数据

具体需求如下：

要统计一个数据表中的数据，将某一列的每个相同的值加和，作为一个新的列。

具体数据如下：

---

id   result
1       2
2       2
3       1
4       2
5       3
6       1

---

统计结果如下,大概意思吧。

id          result1   result2   result3
1               2       3           1

其中id关联别的表
具体的SQL如下，仅供参考：

SELECT c.agentName, c.agentCompany,
SUM(IF(a1.id is not null,1,0)) as 't1',
SUM(IF(a2.id is not null,1,0)) as 't2',
SUM(IF(a3.id is not null,1,0)) as 't3',
SUM(IF(a4.id is not null,1,0)) as 't4'
from agentorder b INNER JOIN agent c on b.agentId = c.id
LEFT JOIN orderlog a1 on a1.orderId = b.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3000'
LEFT JOIN orderlog a2 on a2.orderId = b.id and a2.createTime >= '2016-06-01 00:00:00' and a2.createTime <= '2016-06-30 23:59:59' and a2.checkStep = '3001'
LEFT JOIN orderlog a3 on a3.orderId = b.id and a3.createTime >= '2016-06-01 00:00:00' and a3.createTime <= '2016-06-30 23:59:59' and a3.checkStep = '6000'
LEFT JOIN orderlog a4 on a4.orderId = b.id and a4.createTime >= '2016-06-01 00:00:00' and a4.createTime <= '2016-06-30 23:59:59' and a4.checkStep = '2000'
GROUP BY c.agentName

方法2
SELECT b.agentName as '房管员', b.agentCompany as '公司',
(select count(*) from orderlog a1, order b1 where a1.orderId = b1.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3000' and b1.agentId =b.id) as '重传合同',
(select count(*) from orderlog a2, order b2 where a2.orderId = b2.id and a2.createTime >= '2016-06-01 00:00:00' and a2.createTime <= '2016-06-30 23:59:59' and a2.checkStep = '3001' and b2.agentId =b.id) as '重传身份证',
(select count(*) from orderlog a3, order b3 where a3.orderId = b3.id and a3.createTime >= '2016-06-01 00:00:00' and a3.createTime <= '2016-06-30 23:59:59' and a3.checkStep = '6000' and b3.agentId =b.id) as '清退',
(select count(*) from orderlog a4, order b4 where a4.orderId = b4.id and a4.createTime >= '2016-06-01 00:00:00' and a4.createTime <= '2016-06-30 23:59:59' and a4.checkStep = '3000' and b4.agentId =b.id) as '推单'
from agent b

方法3
SELECT b.agentName as '房管员', b.agentCompany as '公司',
(select count(*) from orderlog a1, order a2 where a1.orderId = a2.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3000' and a2.agentId =b.id) as '重传合同',
(select count(*) from orderlog a1, order a2 where a1.orderId = a2.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3001' and a2.agentId =b.id) as '重传身份证',
(select count(*) from orderlog a1, order a2 where a1.orderId = a2.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '6000' and a2.agentId =b.id) as '清退',
(select count(*) from orderlog a1, order a2 where a1.orderId = a2.id and a1.createTime >= '2016-06-01 00:00:00' and a1.createTime <= '2016-06-30 23:59:59' and a1.checkStep = '3000' and a2.agentId =b.id) as '推单'
from agent b