mysql - Relative frequency by category SQL queries -

i have table columns: date, category, views.

i'd write query gives me following output:

year, quarter, category, "relative percentage of category views quarter"

for example:

2013, q1, blue, 0.15 2013, q1, yellow, 0.05 2013, q1, green, 0.80 2013, q2, blue, 0.20 2013, q2, yellow, 0.05 2013, q2, green, 0.75 2013, q3, blue, 0.10 2013, q3, yellow, 0.15 2013, q3, green, 0.65 

note each quarter relative frequencies add 1 across set of categories.

is state handle dynamically sql (i'm using mysql)?

this have far.

it's want, except relative frequencies output sum 1 across quarters.

set @total_views = select sum(views) daily_views_by_category;  select     year(dt),     quarter(dt),     category,     sum(views)/@total_views category_views     daily_views_by_category group     year(dt), quarter(dt), category order     year(dt), quarter(dt), category ; 

you want join rather variable. idea summarize data year-quarter-catalog , year-quarter , join results arithmetic:

select yqc.yr, yqc.qt, category,        (yqc.views / yq.views) category_views (select year(dt) yr, quarter(dt) qt, category, sum(views) views       daily_views_by_category vbc       group year(dt), quarter(dt), category      ) yqc join      (select year(dt) yr, quarter(dt) qt, sum(views) views       daily_views_by_category vbc       group year(dt), quarter(dt)      ) yq      on yqc.yr = yq.yr , yqc.qt = yq.qt order 1, 2, 3;