查询部门名称、统计数值和FEEDBACKSTATE ==‘Y’的总值以及总值和统计直的100倍的商,并根据部门名称对数据进行分组,并根据分组的统计值进行排序
SELECT
DEPTNAME,
COUNT( DEPTNAME ) AS count,
sum( CASE WHEN FEEDBACKSTATE = ‘Y’ THEN 1 ELSE 0 END ) AS reply,
sum( CASE WHEN FEEDBACKSTATE = ‘Y’ THEN 1 ELSE 0 END ) / COUNT( DEPTNAME )*100 AS percentage
FROM
VS_FEEDBACK
因为要根据部门名称分组,所以要在后面加上GROUP BY DEPTNAME,
并且根据统计值排序: ORDER BY count DESC;
这样根据DEPTNAME进行分组,并根据分组的统计值进行排序,如果FEEDBACKSTATE = 'Y’则为1,否则就是0,sum对每次的1或0进行累加的完整SQL语句就是:
SELECT
DEPTNAME,
COUNT( DEPTNAME ) AS count,
sum( CASE WHEN FEEDBACKSTATE = ‘Y’ THEN 1 ELSE 0 END ) AS reply,
sum( CASE WHEN FEEDBACKSTATE = ‘Y’ THEN 1 ELSE 0 END ) / COUNT( DEPTNAME )*100 AS percentage
FROM
VS_FEEDBACK where DEPTNAME IS NOT NULL and areacode= ‘621100000000