+---------+----------+-------------------------------------------------------+
| user_id | date_id | reward_yn_list |
+---------+----------+-------------------------------------------------------+
| 21705 | 20161004 | Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N |
| 22185 | 20161004 | N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N |
| 23121 | 20161004 | Y,Y,N,Y,N,Y,N,Y,Y,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N |
| 23641 | 20161004 | Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,N,Y,N,N,N,N,N |
| 23729 | 20161004 | Y,Y,N,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,N,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,N,N |
+---------+----------+-------------------------------------------------------+
SELECT s.country,
SUM(s.d1) AS d1,
SUM(s.d2) AS d2,
SUM(s.d3) AS d3,
SUM(s.d4) AS d4,
SUM(s.d5) AS d5,
SUM(s.d6) AS d6,
SUM(s.d7) AS d7,
SUM(s.d8) AS d8,
SUM(s.d9) AS d9,
SUM(s.d10) AS d10,
SUM(s.d11) AS d11,
SUM(s.d12) AS d12,
SUM(s.d13) AS d13,
SUM(s.d14) AS d14,
SUM(s.d15) AS d15,
SUM(s.d16) AS d16,
SUM(s.d17) AS d17,
SUM(s.d18) AS d18,
SUM(s.d19) AS d19,
SUM(s.d20) AS d20,
SUM(s.d21) AS d21,
SUM(s.d22) AS d22,
SUM(s.d23) AS d23,
SUM(s.d24) AS d24,
SUM(s.d25) AS d25,
SUM(s.d26) AS d26,
SUM(s.d27) AS d27
FROM
(SELECT
(
CASE WHEN a.country = 'KR' THEN 'KR'
when a.country = 'US' THEN 'US'
when a.country = 'TW' THEN 'TW'
when a.country = 'JP' THEN 'JP'
ELSE 'ETC'
END
) AS country,
(CASE WHEN T.d1 = 'Y' THEN 1 ELSE 0 END) AS d1,
(CASE WHEN T.d2 = 'Y' THEN 1 ELSE 0 END) AS d2,
(CASE WHEN T.d3 = 'Y' THEN 1 ELSE 0 END) AS d3,
(CASE WHEN T.d4 = 'Y' THEN 1 ELSE 0 END) AS d4,
(CASE WHEN T.d5 = 'Y' THEN 1 ELSE 0 END) AS d5,
(CASE WHEN T.d6 = 'Y' THEN 1 ELSE 0 END) AS d6,
(CASE WHEN T.d7 = 'Y' THEN 1 ELSE 0 END) AS d7,
(CASE WHEN T.d8 = 'Y' THEN 1 ELSE 0 END) AS d8,
(CASE WHEN T.d9 = 'Y' THEN 1 ELSE 0 END) AS d9,
(CASE WHEN T.d10 = 'Y' THEN 1 ELSE 0 END) AS d10,
(CASE WHEN T.d11 = 'Y' THEN 1 ELSE 0 END) AS d11,
(CASE WHEN T.d12 = 'Y' THEN 1 ELSE 0 END) As d12,
(CASE WHEN T.d13 = 'Y' THEN 1 ELSE 0 END) AS d13,
(CASE WHEN T.d14 = 'Y' THEN 1 ELSE 0 END) AS d14,
(CASE WHEN T.d15 = 'Y' THEN 1 ELSE 0 END) AS d15,
(CASE WHEN T.d16 = 'Y' THEN 1 ELSE 0 END) AS d16,
(CASE WHEN T.d17 = 'Y' THEN 1 ELSE 0 END) AS d17,
(CASE WHEN T.d18 = 'Y' THEN 1 ELSE 0 END) AS d18,
(CASE WHEN T.d19 = 'Y' THEN 1 ELSE 0 END) AS d19,
(CASE WHEN T.d20 = 'Y' THEN 1 ELSE 0 END) AS d20,
(CASE WHEN T.d21 = 'Y' THEN 1 ELSE 0 END) AS d21,
(CASE WHEN T.d22 = 'Y' THEN 1 ELSE 0 END) AS d22,
(CASE WHEN T.d23 = 'Y' THEN 1 ELSE 0 END) AS d23,
(CASE WHEN T.d24 = 'Y' THEN 1 ELSE 0 END) AS d24,
(CASE WHEN T.d25 = 'Y' THEN 1 ELSE 0 END) AS d25,
(CASE WHEN T.d26 = 'Y' THEN 1 ELSE 0 END) AS d26,
(CASE WHEN T.d27 = 'Y' THEN 1 ELSE 0 END) AS d27
FROM DB_name_1.user AS a,
(
SELECT user_id,
SUBSTRING(reward_yn_list, 1, 1) AS d1,
SUBSTRING(reward_yn_list, 3, 1) AS d2,
SUBSTRING(reward_yn_list, 5, 1) AS d3,
SUBSTRING(reward_yn_list, 7, 1) AS d4,
SUBSTRING(reward_yn_list, 9, 1) AS d5,
SUBSTRING(reward_yn_list, 11, 1) AS d6,
SUBSTRING(reward_yn_list, 13, 1) AS d7,
SUBSTRING(reward_yn_list, 15, 1) AS d8,
SUBSTRING(reward_yn_list, 17, 1) AS d9,
SUBSTRING(reward_yn_list, 19, 1) AS d10,
SUBSTRING(reward_yn_list, 21, 1) AS d11,
SUBSTRING(reward_yn_list, 23, 1) AS d12,
SUBSTRING(reward_yn_list, 25, 1) AS d13,
SUBSTRING(reward_yn_list, 27, 1) AS d14,
SUBSTRING(reward_yn_list, 29, 1) AS d15,
SUBSTRING(reward_yn_list, 31, 1) AS d16,
SUBSTRING(reward_yn_list, 33, 1) AS d17,
SUBSTRING(reward_yn_list, 35, 1) AS d18,
SUBSTRING(reward_yn_list, 37, 1) AS d19,
SUBSTRING(reward_yn_list, 39, 1) AS d20,
SUBSTRING(reward_yn_list, 41, 1) AS d21,
SUBSTRING(reward_yn_list, 43, 1) AS d22,
SUBSTRING(reward_yn_list, 45, 1) AS d23,
SUBSTRING(reward_yn_list, 47, 1) AS d24,
SUBSTRING(reward_yn_list, 49, 1) AS d25,
SUBSTRING(reward_yn_list, 51, 1) AS d26,
SUBSTRING(reward_yn_list, 53, 1) AS d27
FROM DB_name_1.evt_reward) AS T
WHERE T.user_id = a.user_id) s
GROUP BY s.country
;
