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  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_count"
FROM
	member
GROUP BY
	"age_group"
ORDER BY 
	"age_group"