SELECT
s.action_no
,SUM(s.count_uu) AS total_uu
,CEIL(SUM(s.point * count_uu) / SUM(s.count_uu)) AS ave_point
,SUM(CASE WHEN FLOOR(s.point / 10) = 0 THEN count_uu ELSE 0 END) AS point_00
,SUM(CASE WHEN FLOOR(s.point / 10) = 1 THEN count_uu ELSE 0 END) AS point_01
,SUM(CASE WHEN FLOOR(s.point / 10) = 2 THEN count_uu ELSE 0 END) AS point_02
,SUM(CASE WHEN FLOOR(s.point / 10) = 3 THEN count_uu ELSE 0 END) AS point_03
,SUM(CASE WHEN FLOOR(s.point / 10) = 4 THEN count_uu ELSE 0 END) AS point_04
,SUM(CASE WHEN FLOOR(s.point / 10) = 5 THEN count_uu ELSE 0 END) AS point_05
,SUM(CASE WHEN FLOOR(s.point / 10) = 6 THEN count_uu ELSE 0 END) AS point_06
,SUM(CASE WHEN FLOOR(s.point / 10) = 7 THEN count_uu ELSE 0 END) AS point_07
,SUM(CASE WHEN FLOOR(s.point / 10) = 8 THEN count_uu ELSE 0 END) AS point_08
,SUM(CASE WHEN FLOOR(s.point / 10) = 9 THEN count_uu ELSE 0 END) AS point_09
,SUM(CASE WHEN FLOOR(s.point / 10) = 10 THEN count_uu ELSE 0 END) AS point_10
,SUM(CASE WHEN FLOOR(s.point / 10) = 11 THEN count_uu ELSE 0 END) AS point_11
,SUM(CASE WHEN FLOOR(s.point / 10) = 12 THEN count_uu ELSE 0 END) AS point_12
,SUM(CASE WHEN FLOOR(s.point / 10) = 13 THEN count_uu ELSE 0 END) AS point_13
,SUM(CASE WHEN FLOOR(s.point / 10) = 14 THEN count_uu ELSE 0 END) AS point_14
,SUM(CASE WHEN FLOOR(s.point / 10) = 15 THEN count_uu ELSE 0 END) AS point_15
,SUM(CASE WHEN FLOOR(s.point / 10) = 16 THEN count_uu ELSE 0 END) AS point_16
,SUM(CASE WHEN FLOOR(s.point / 10) = 17 THEN count_uu ELSE 0 END) AS point_17
,SUM(CASE WHEN FLOOR(s.point / 10) = 18 THEN count_uu ELSE 0 END) AS point_18
,SUM(CASE WHEN FLOOR(s.point / 10) = 19 THEN count_uu ELSE 0 END) AS point_19
,SUM(CASE WHEN FLOOR(s.point / 10) = 20 THEN count_uu ELSE 0 END) AS point_20
# 全体集計
FROM
(
SELECT
SUBSTRING_INDEX(action, ',', 1) AS action_no
,CEIL(SUBSTRING_INDEX(action, ',', -1)) AS point
,COUNT(action) AS count_uu
FROM
acts
WHERE
sub = 'practice'
AND
time >= '$date 00:00:00'
AND
LENGTH(SUBSTRING_INDEX(action, ',', 1)) > 0
AND
CEIL(SUBSTRING_INDEX(action, ',', -1)) BETWEEN 0 AND 200
GROUP BY
action_no
,point DIV 10
ORDER BY
action_no
) AS s
GROUP BY
s.action_no;