MySQL sum based on a condition -

MySQL sum based on a condition -

i have info in table gives raw events , count of products this:

|order_id|customer_id|product_count| |1 |1 |2 | |2 |1 |-1 | |3 |1 |3 | |4 |1 |-1 | |5 |2 |-1 | |6 |2 |2 | |7 |2 |-1 | |8 |3 |-1 | |9 |3 |-1 | |10 |3 |-1 |

i want results this: (only sum values after first positive value)

|customer_id|sum(remaining_product)| |1 |3 | |2 |1 | |3 |0 |

using query can homecoming of sums:

select customer_id, sum(product_count) table grouping customer_id;

what need runing sum of product_count, should start after positive value. in case of client 1, first order postiive 2-1+3-1=3 cutomer 2 , first order negative ignore , start 2-1=1 client 3 , never see positive value remain @ 0.

i approached starting subquery retrieves minimum positive row each client id. simple plenty using min() function:

select min(order_id) minorderid, customer_id mytable product_count > 0 grouping customer_id

then joined original table rows on or after order_id each group. way can sum rows origin after positive row. query becomes this:

select t.customer_id, sum(t.product_count) mytable t bring together (select min(order_id) minorderid, customer_id mytable product_count > 0 grouping customer_id) w on w.customer_id = t.customer_id , t.order_id >= w.minorderid grouping t.customer_id;

you have bring together tables on customer_id (naturally) on status order_id greater or equal minimum order_id positive product count. way rows after summed.

if aren't concerned customer_ids 0, query enough. however, if want 0 rows show well, outer bring together query pulls each of client ids. need ifnull() function check customer_ids not have row in sec query above.

finally, have this:

select k.customer_id, ifnull(f.productsum, 0) sumofproducts from(select distinct customer_id mytable) k left join(select t.customer_id, sum(t.product_count) productsum mytable t bring together (select min(order_id) minorderid, customer_id mytable product_count > 0 grouping customer_id) w on w.customer_id = t.customer_id , t.order_id >= w.minorderid grouping t.customer_id) f on k.customer_id = f.customer_id;

and believe or not works. here sql fiddle.

mysql sum