gistfile1.sql

SELECT
-- 年齢範囲ごとのフラグ
CASE
	when date_part('year', age('2010-12-1',birthday)) between 1 and 19 then 10
	when date_part('year', age('2010-12-1',birthday)) between 20 and 29 then 20
	when date_part('year', age('2010-12-1',birthday)) between 30 and 39 then 30
	when date_part('year', age('2010-12-1',birthday)) between 40 and 49 then 40
	when date_part('year', age('2010-12-1',birthday)) between 50 and 59 then 50
	when date_part('year', age('2010-12-1',birthday)) between 60 and 69 then 60
	when date_part('year', age('2010-12-1',birthday)) between 70 and 79 then 70
	when date_part('year', age('2010-12-1',birthday)) between 80 and 89 then 80
	when date_part('year', age('2010-12-1',birthday)) between 90 and 99 then 90
	when date_part('year', age('2010-12-1',birthday)) >=100 then 100
end AS "age_group",

-- 年齢フラグごとのカウント数(男性）
	count(
		case
		when date_part('year', age('2010-12-1',birthday)) between 1 and 19 and sex = true then '10'
		when date_part('year', age('2010-12-1',birthday)) between 20 and 29 and sex = true then '20'
		when date_part('year', age('2010-12-1',birthday)) between 30 and 39 and sex = true then '30'
		when date_part('year', age('2010-12-1',birthday)) between 40 and 49 and sex = true then '40'
		when date_part('year', age('2010-12-1',birthday)) between 50 and 59 and sex = true then '50'
		when date_part('year', age('2010-12-1',birthday)) between 60 and 69 and sex = true then '60'
		when date_part('year', age('2010-12-1',birthday)) between 70 and 79 and sex = true then '70'
		when date_part('year', age('2010-12-1',birthday)) between 80 and 89 and sex = true then '80'
		when date_part('year', age('2010-12-1',birthday)) between 90 and 99 and sex = true then '90'
		when date_part('year', age('2010-12-1',birthday)) >=100 and sex = true then '100'
		end
	) as "men",
-- 年齢フラグごとのカウント数(女性）
	count(
		case
		when date_part('year', age('2010-12-1',birthday)) between 1 and 19 and sex = false then '10'
		when date_part('year', age('2010-12-1',birthday)) between 20 and 29 and sex = false then '20'
		when date_part('year', age('2010-12-1',birthday)) between 30 and 39 and sex = false then '30'
		when date_part('year', age('2010-12-1',birthday)) between 40 and 49 and sex = false then '40'
		when date_part('year', age('2010-12-1',birthday)) between 50 and 59 and sex = false then '50'
		when date_part('year', age('2010-12-1',birthday)) between 60 and 69 and sex = false then '60'
		when date_part('year', age('2010-12-1',birthday)) between 70 and 79 and sex = false then '70'
		when date_part('year', age('2010-12-1',birthday)) between 80 and 89 and sex = false then '80'
		when date_part('year', age('2010-12-1',birthday)) between 90 and 99 and sex = false then '90'
		when date_part('year', age('2010-12-1',birthday)) >=100 and sex = false then '100'
		end
	) as "women"
FROM
member
GROUP BY
"age_group"
ORDER BY
"age_group"