How to return array while using GROUP BY

Question

Right now, I have this query:

SELECT COUNT(*) AS Count, SUM(Ask) AS Ask, SUM(Cost) AS Cost, Provider, Factura FROM store_items 
    WHERE (
      Provider NOT IN(SELECT Provider FROM store_provider_invoices)
      AND Factura NOT IN(SELECT Factura FROM store_provider_invoices)
    ) 
    OR Factura NOT IN(SELECT Factura FROM store_provider_invoices) 
    GROUP BY Provider, Factura

This is working great, and returns the 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 I would like to do is to also return all the ID's that match the query from the 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, for instance, in the last array element above, instead of just returning a Count of 3, also return the ID's of each row that it counted.

Uueerdo · Accepted Answer · 2016-02-05 22:32:16Z

4

You can't really get nested results, but you could use GROUP_CONCAT(DISTINCT store_items.ID ORDER BY ID) AS siIDs to get a comma-separated list of id values.

You can also modify the separator

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

answered Feb 5, 2016 at 22:32

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MultiDev Over a year ago

That's great I never knew about this before. Thank you.

Collectives™ on Stack Overflow

How to return array while using GROUP BY

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related