在一个表SQL的不同列中计数值
在一个表SQL的不同列中计数值
提问于 2016-11-27 22:30:12
例如，我的桌子上有数据--10个志愿者。有两种类型的志愿者-学生和工作人员。如何使用此视图在一个表中插入几个列：
计数(Volunteer_id)，计数(Volunteer_id)在Volunteer_type =‘学生’，计数(Volunteer_id)，其中Volunteer_type =‘工作人员’
SELECT COUNT(Volunteer_id) AS "TOTAL VOLUNTEERS"
from volunteer
UNION
SELECT COUNT(Volunteer_id) AS "TOTAL VOLUNTEERS"
from volunteer
WHERE Volunteer_type = 'Staff'
UNION
SELECT COUNT(Volunteer_id) AS "TOTAL VOLUNTEERS"
from volunteer
WHERE Volunteer_type = 'Student'这个语句现在表示为行，但我想使它们成为列。
回答 3
Stack Overflow用户
回答已采纳
发布于 2016-11-27 22:33:38
使用条件聚合：
SELECT
COUNT(*) AS "Total Volunteers",
COUNT(CASE WHEN Volunteer_tpye = 'Staff' THEN 1 END) AS "Staff Volunteers",
COUNT(CASE WHEN Volunteer_tpye = 'Student' THEN 1 END) AS Student
FROM volunteersStack Overflow用户
发布于 2016-11-27 22:56:15
DECLARE @volunteer TABLE
(Volunteer_id int
,Volunteer_type varchar(50) )
INSERT INTO @volunteer VALUES (1,'Staff')
INSERT INTO @volunteer VALUES (1,'Staff')
INSERT INTO @volunteer VALUES (1,'Staff')
INSERT INTO @volunteer VALUES (1,'Student')
INSERT INTO @volunteer VALUES (1,'Student')
INSERT INTO @volunteer VALUES (1,'Student')
SELECT COUNT(Volunteer_id) AS "TOTAL VOLUNTEERS"
,(
SELECT COUNT(tb1.Volunteer_id) AS "TOTAL VOLUNTEERS"
FROM @volunteer AS tb1
WHERE tb1.Volunteer_id = tb.Volunteer_id
AND tb1.Volunteer_type = 'Staff'
)
,(
SELECT COUNT(volunteer_Student.Volunteer_id) AS "TOTAL VOLUNTEERS"
FROM @volunteer AS volunteer_Student
WHERE volunteer_Student.Volunteer_id = tb.Volunteer_id
AND volunteer_Student.Volunteer_type = 'Student'
)
FROM @volunteer tb
GROUP BY tb.Volunteer_idStack Overflow用户
发布于 2016-11-27 23:32:20
试试这个：
select
sum(t.x_all) cnt_all,
sum(t.x_staff) cnt_staff,
sum(t.x_student) cnt_student
from (
select
1 x_all,
case when v.volunteer_type = 'Staff' then 1 else 0 end x_staff,
case when v.volunteer_type = 'Student' then 1 else 0 end x_student
from volunteer v
) t页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40834692
复制相关文章
相似问题
腾讯云开发者
