mysql - How to return array while using GROUP BY -
right now, have query:
select count(*) count, sum(ask) ask, sum(cost) cost, provider, factura store_items ( provider not in(select provider store_provider_invoices) , factura not in(select factura store_provider_invoices) ) or factura not in(select factura store_provider_invoices) group provider, factura this working great, , returns following array:
array ( [0] => array ( [count] => 1 [id] => 13 [ask] => 20.00 [cost] => 10.00 [provider] => 5 [factura] => 8 ) [1] => array ( [count] => 1 [id] => 18 [ask] => 125.01 [cost] => 110.01 [provider] => 5 [factura] => 34 ) [3] => array ( [count] => 3 [id] => 14 [ask] => 210.00 [cost] => 150.00 [provider] => 6 [factura] => 5 ) ) what return id's match query store_items table, like:
array ( [0] => array ( [id] => array ( [0] => 101 ) [count] => 1 [ask] => 20.00 [cost] => 10.00 [provider] => 5 [factura] => 8 ) [1] => array ( [id] => array ( [0] => 102 ) [count] => 1 [ask] => 125.01 [cost] => 110.01 [provider] => 5 [factura] => 34 ) [3] => array ( [id] => array ( [0] => 103 [1] => 104 [2] => 105 ) [count] => 3 [ask] => 210.00 [cost] => 150.00 [provider] => 6 [factura] => 5 ) ) so, instance, in last array element above, instead of returning count of 3, return id's of each row counted.
you can't nested results, use group_concat(distinct store_items.id order id) siids comma-separated list of id values.
you can modify separator
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
Comments
Post a Comment