在一个查询中计算多列 [英] count multiple columns in one query
本文介绍了在一个查询中计算多列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
伙计,我有四个查询:
查询#1:
select
satisfaction_score,count(satisfaction_score) as Satisfaction_count
from j_survey_response
where satisfaction_score != 0
group by satisfaction_score
输出将为
satisfaction_score Satisfaction_count
1 5
2 8
3 97
4 329
5 859
查询2:
select
response_score,count(response_score) as response_count
from j_survey_response
where response_score != 0
group by response_score
输出
response_score response_count
1 28
2 8
3 42
4 250
5 980
查询3:
select
responder_score,count(responder_score) as responder_count
from j_survey_response
where responder_score != 0
group by responder_score
输出
responder_score responder_count
1 24
2 3
3 30
4 236
5 987
查询4:
select
service_score,count(service_score) as service_count
from j_survey_response
where service_score != 0
group by service_score
输出
service_score service_count
1 22
2 2
3 34
4 270
5 966
但是我需要以下输出
score satisfaction_count response_count responder_count service_count
1 5 28 24 22
2 8 8 3 2
3 97 42 30 34
4 329 250 236 270
5 859 980 986 966
推荐答案
您可以UNION ALL
单独的查询,并对结果集应用有条件的聚合:
You can UNION ALL
the separate queries and apply conditional aggregation on the resulting set:
select score,
max(case when type = 'satisfaction' then count end) as satisfaction_count,
max(case when type = 'response' then count end) as response_count,
max(case when type = 'responder' then count end) as responder_count,
max(case when type = 'service' then count end) as service_count
from (
select satisfaction_score as score,
count(satisfaction_score) as count,
'satisfaction' as type
from j_survey_response
where satisfaction_score != 0
group by satisfaction_score
union all
select response_score,
count(response_score) as count, 'response' as type
from j_survey_response
where response_score != 0
group by response_score
union all
select responder_score,
count(responder_score) as count, 'responder' as type
from j_survey_response
where responder_score != 0
group by responder_score
union all
select service_score,
count(service_score) as count, 'service' as type
from j_survey_response
where service_score != 0
group by service_score) as t
group by score
这篇关于在一个查询中计算多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文