mysql - Select multiple fields from subquery -

mysql - Select multiple fields from subquery -

i have next query:

select a.date, (select sum(used), sum(max) switch_statistic b date = (select max(date) switch_statistic switch_id = b.switch_id , date <= a.date)) switch_statistic grouping date;

as see need select sum(used), sum(max) subquery. concat not solution!

table schema:

id --- switch_id --- date --- max --- used

some data:

1 641 2014-10-04 2 16 20 630 2014-10-04 1 7 24 634 2014-10-04 0 8 26 641 2014-10-06 2 16 32 641 2014-10-07 2 16 35 641 2014-10-08 3 16 39 641 2014-10-09 2 16 64 293 2014-10-10 1 22 ... 557 38 2014-10-12 3 22 559 293 2014-10-12 1 22 563 294 2014-10-12 6 22 565 641 2014-10-12 2 16

what need: illustration concat_ws

mysql> select a.date, (select concat_ws('/', sum(used), sum(max)) switch_statistic b date = (select max(date) switch_statistic switch_id = b.switch_id , date <= a.date)) result switch_statistic grouping date; +------------+----------+ | date | result | +------------+----------+ | 2014-10-04 | 3/31 | | 2014-10-06 | 3/31 | | 2014-10-07 | 3/31 | | 2014-10-08 | 4/31 | | 2014-10-09 | 3/31 | | 2014-10-10 | 249/1587 | | 2014-10-11 | 354/2147 | | 2014-10-12 | 360/2185 | +------------+----------+ 8 rows in set (0.26 sec)

query logic: 1) select date's table 2) sum - used , max current date, if switch_id don't have record date, select lastly exists in table

link sqlfiddle - http://sqlfiddle.com/#!2/c3d479

you should able aggregation , no subqueries or joins:

select date, sum(used) used, sum(max) max switch_statistic ss ss.date = (select max(date) switch_statistics ss2 ss2.switch_id = ss.switchid grouping ss.date;

edit:

you seem want cumulative sum. in mysql, best done using variables:

select date, used, max, (@u := @u + used) cumeused, @m := @m + max) cumemax (select date, sum(used) used, sum(max) max switch_statistic ss grouping ss.date ) ss cross bring together (select @u := 0, @m := 0) vars order date;

mysql sql