group multiple rows

Question

This is my table:

BOX  ITEM
1    0000001
1    0000002
1    0000003
1    0000004
2    1111111
2    1111111
2    1111111
2    1111111
3    0000001
3    0000002
3    0000003
3    0000004
4    0000001
4    0000002
4    0000003

Which means:

• Box 1 has 4 different items.
• Box 2 has 4 items.
• Box 3 has 4 items.
• Box 4 has 3 items.

Attention!

• Box 1 and box 3 contains exactly the same items, so we can group them.
• Box 4 contains almost the same as box 1 and box 3 but not really the same, thus, we can't group with box 1 and box 3.

Now the question;

I want to group all the boxes with exactly the same content, having a count of the amount of same boxes, like this, but any other approach is valid, this is just an example to ilustrate my needs:

QUANTITY   ITEM       BOX
   2       0000001    1, 3
   2       0000002    1, 3
   2       0000003    1, 3
   2       0000004    1, 3
   1       1111111    2
   1       1111111    2
   1       1111111    2
   1       1111111    2
   1       0000001    4
   1       0000002    4
   1       0000003    4

(That means, I have 2 boxes with the items 0000001, 0000002, 0000003 and 0000004, which are box 1 and box 3.

I have 1 box with the items 111111, 111111... etc etc)

And I can't find a way to do it... Any help?

Answer 1

If you are working in SQL Server, you need to use something like this for the group_concat of mySQL http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist. The mySQL solution and this one both assume that the order of items in each box is always the same or the items would need to be sorted before they are concatenated so you can compare the contents of each box. THis ends up looking pretty messy with all the nesting but it produces the desired output:

SELECT  count(box) NumBoxes, 
        list Contents, 
        BoxList = substring((SELECT ( ', ' + cast(box as varchar) )
                             FROM (SELECT   b1.box,
                                            List = substring((SELECT ( ', ' + item )
                                                              FROM boxes b2
                                                              WHERE b1.box = b2.box
                                                              ORDER BY box, item
                                                               FOR XML PATH( '' )
                                                            ), 3, 1000 )
                                    FROM boxes b1
                                    GROUP BY box
                                   ) source2
                             WHERE source1.list  = source2.list
                             ORDER BY box, list
                             FOR XML Path( '' )
                           ), 3, 1000 )
FROM(SELECT b1.box,
            List = substring((SELECT ( ', ' + item )
                              FROM boxes b2
                              WHERE b1.box = b2.box
                              ORDER BY box, item
                              FOR XML PATH( '' )
                            ), 3, 1000 )
      FROM boxes b1
      GROUP BY box
    ) source1
GROUP BY list

by the way, for testing I used this table, and the fact that BOX was INT datatype required the cast in the third line of code above:

create table boxes(
BOX int null,  
ITEM char(7) null)
go
insert into boxes
values(1,'0000001'),
(1,'0000002'),
(1,'0000003'),
(1,'0000004'),
(2,'1111111'),
(2,'1111111'),
(2,'1111111'),
(2,'1111111'),
(3,'0000001'),
(3,'0000002'),
(3,'0000003'),
(3,'0000004'),
(4,'0000001'),
(4,'0000002'),
(4,'0000003')
go

Answer 2

Assuming this is MySQL, try:

select count(distinct box) quantity, items, group_concat(box) boxes 
from
(select box, group_concat(item) items
 from my_table
 group by box) v
group by items