i have table in sqlite called param_vals_breaches looks following:
id param queue date_time param_val breach_count 1 c 2013-01-01 00:00:00 188 7 2 c b 2013-01-01 00:00:00 156 8 3 c c 2013-01-01 00:00:00 100 2 4 d 2013-01-01 00:00:00 657 0 5 d b 2013-01-01 00:00:00 23 6 6 d c 2013-01-01 00:00:00 230 12 7 c 2013-01-01 01:00:00 100 0 8 c b 2013-01-01 01:00:00 143 9 9 c c 2013-01-01 01:00:00 12 2 10 d 2013-01-01 01:00:00 0 1 11 d b 2013-01-01 01:00:00 29 5 12 d c 2013-01-01 01:00:00 22 14 13 c 2013-01-01 02:00:00 188 7 14 c b 2013-01-01 02:00:00 156 8 15 c c 2013-01-01 02:00:00 100 2 16 d 2013-01-01 02:00:00 657 0 17 d b 2013-01-01 02:00:00 23 6 18 d c 2013-01-01 02:00:00 230 12 i want write query show me particular queue (e.g. "a") average param_val , breach_count each param on hour hour basis. transposing data looks this:
results queue hour 0 hour 0 hour 1 hour 1 hour 2 hour 2 param avg_param_val avg_breach_count avg_param_val avg_breach_count avg_param_val avg_breach_count c xxx xxx xxx xxx xxx xxx d xxx xxx xxx xxx xxx xxx is possible? i'm not sure how go it. thanks!
sqlite not have pivot function can use aggregate function case expression turn rows columns:
select param, avg(case when time = '00' param_val end) avghour0val, avg(case when time = '00' breach_count end) avghour0count, avg(case when time = '01' param_val end) avghour1val, avg(case when time = '01' breach_count end) avghour1count, avg(case when time = '02' param_val end) avghour2val, avg(case when time = '02' breach_count end) avghour2count ( select param, strftime('%h', date_time) time, param_val, breach_count param_vals_breaches queue = 'a' ) src group param; see sql fiddle demo
Comments
Post a Comment