1、这里不需要再额外做百分比了,因为A_STRONGLY_AGREE显示的数值就是百分比,不清楚的可以用
SELECT * FROM nss 看一下
SELECT A_STRONGLY_AGREE
FROM nss
WHERE question='Q01'
AND institution='Edinburgh Napier University'
AND subject='(8) Computer Science'
2
SELECT institution,subject
FROM nss
WHERE question='Q15'
AND score>=100
3
SELECT institution,score
FROM nss
WHERE question='Q15'
AND score<50
AND subject='(8) Computer Science'
4
SELECT subject, sum(response)
FROM nss
WHERE question='Q22' AND
(subject='(8) Computer Science'
OR subject= '(H) Creative Arts and Design')
GROUP BY subject
5、这个问题在于求总人数,而强烈同意的是占比,所以要乘上回答的总人数*0.01,但是由于乘上之后会有小数,需要floor函数先把每个数向下取整,再加和。
SELECT subject, sum( floor(A_STRONGLY_AGREE*response*0.01))
FROM nss
WHERE question='Q22'
AND (subject='(8) Computer Science'
OR subject='(H) Creative Arts and Design')
GROUP BY subject
6、这里用round其实是有异议的,因为round是四舍五入,如果在上一题中用round而不是floor,值会差很多。第2种方法勉强可以做出和1同样的结果,但是我认为1更符合。
SELECT subject,
floor(sum(floor(A_STRONGLY_AGREE*response*0.01))/sum(response)*100)
FROM nss
WHERE question='Q22'
AND (subject='(8) Computer Science'
OR subject='(H) Creative Arts and Design')
GROUP BY subject
SELECT subject,
floor(sum(round(A_STRONGLY_AGREE*response*0.01,0))/sum(response)*100)
FROM nss
WHERE question='Q22'
AND (subject='(8) Computer Science'
OR subject='(H) Creative Arts and Design')
GROUP BY subject
7、这里求平均不能用avg,因为avg是把所有平均分数(score)再平均,而这里是要把所有分数加和后再平均。
SELECT institution,round(sum(score*response)/sum(response),0)
FROM nss
WHERE question='Q22'
AND (institution LIKE '%Manchester%')
GROUP BY institution
8、两种条件求出两个合计数,思路就是分别求出两个数据再拼合成一张表,没有想到什么更简便的方案。用union的话是竖向合并,这里不合适。
SELECT A.institution,A.sample,B.student FROM
(select institution, sum(sample) as sample
from nss
where question='Q01'
and (institution like '%Manchester%')
group by institution) AS A
JOIN
(select institution, sum(sample) as student
from nss
where question='Q01'
and (institution like '%Manchester%')
and (subject like '%Computer%')
group by institution) AS B
ON (A.institution=B.institution)