vendredi 17 juin 2016

Counting grouped and concatinated results

I'm trying to make database that contains products in storage. Every product contains unknown amount of possible "selections" that defines what version of product is in storage.

For example product Sofa that contains selections Color: Black, Legs: Wood and Material: Leather while product Bed contains only selection Mattress: Hard.

I've used GROUP_CONCAT to get all sofas that are same kind like this:

SELECT DISTINCT p.product, p.size, p.color,p.storage,
GROUP_CONCAT(s.selection, ': ' ,s.option) AS selection
FROM product p
    INNER JOIN selection s
       ON p.id = s.product_id
WHERE product.id = 123
GROUP BY s.id;

The question is how do I count how many products was concatenated in query or is it even possible to count with single query?

EDIT:

SELECT DISTINCT product_storage.product, product_storage.size,
product_storage.color, product_storage.storage,
GROUP_CONCAT(product_storage_selection.selection, 
product_storage_selection.option) as selection
FROM product_storage_link
INNER JOIN product_storage 
ON product_storage_link.product_storage_id = product_storage.id
INNER JOIN product_storage_selection 
ON product_storage.id = product_storage_selection.product_storage_id
WHERE product_storage_link.product_id = 7207
GROUP BY product_storage.id;

Gives

Product    Color    Size    Selection
Product1   Black    10x10   Selection1:Option1, Selection2:Option1
Product1   Black    10x10   Selection1:Option2, Selection2:Option1
Product1   White    10x10   Selection1:Option1, Selection2:Option1

I try to get count of duplicates in these two tables and got same selections from other table. Hope this clears the question.

So the goal is to get for example:

Product    Color    Size    Selection                                amount
Product1   Black    10x10   Selection1:Option1, Selection2:Option1   3
Product1   Black    10x10   Selection1:Option2, Selection2:Option1   1
Product1   White    10x10   Selection1:Option1, Selection2:Option1   2

The part from database:

http://sqlfiddle.com/#!9/fec0b

Aucun commentaire:

Enregistrer un commentaire