MYSQL GROUP BY Statement

Databases
1

Hey guys.

I really need your help with this one. Hope you have some time over.

I have these two tables…



Products table:



ID            Manufacturer            Product_Name
------------------------------------------------------------
1               Nike                Air Max Talldress
2               Adidas              High Voltage 
3               Puma                Black Morris Shortdress
4               Nike                Jordani Sway



InStock table:





           (Products table)
ID            Product_ID            Color            Size            Quantity 
----------------------------------------------------------------------------------------------
1                 1                 Black           Medium              61
2                 1                 Black           Large               26
3                 1                 Purple          Small               37
4                 2                 Green           Medium              74
5                 3                 Yellow          Medium             101
6                 4                 White           Large               22




When I use this statement:

SELECT Product_ID, Manufacturer, Product_Name, Color, GROUP_CONCAT(Size) AS Sizes 

FROM InStock JOIN Products ON Products.ID = Product_ID 

WHERE Color IN ('Black') 
      
GROUP BY Product_ID, Color

...I get this result 
Product_ID            Manufacturer            Product_Name            Color            Sizes            
----------------------------------------------------------------------------------------------
    1                    Nike               Air Max Talldress         Black         Medium, Large

In this case, that's exactly what I want, but...
When I add 'AND Size IN('Medium')' to the 'WHERE Clause' like this: "WHERE Color IN ('Black') AND Size IN ('Medium')" I get the same result but without 'Large' under Sizes.

I need to do something similar that returns the result set like above but with both sizes(all sizes) for that product, even though im searching for black and ONLY ‘Medium’.


Any ideas?

Bless
//Neo

2

OK, seems confusing to me because if you’re looking only for black shoes in medium, why do you care if there are large sizes for that shoe.

But if you’re looking to return all sizes for a colors, then you’re going to need to sub-query the group by query. Notice the change to the Sizes where clause (now a LIKE instead of an IN since you’re dealing with a group concatenated field)

SELECT Product_ID
      , Manufacturer
      , Product_Name
      , Color
      , Sizes 
  FROM (SELECT Product_ID
             , Manufacturer
             , Product_Name
             , Color
             , GROUP_CONCAT(Size) AS Sizes 
          FROM InStock 
         INNER JOIN JOIN Products ON Products.ID = Product_ID 
         GROUP BY Product_ID, Color) SQ
  WHERE Color = ('Black') 
    AND Sizes LIKE '%Medium%'
1 Like
3

you could also use the FIND_IN_SET function –

AND FIND_IN_SET('Medium',Sizes) > 0
1 Like
4

Is there a performance benefit to that? Just curious since I’ve never seen that form before.

5

pretty sure both will do a table scan

6

Hey guys. The answers were very helpful, thanks alot, really!


What I’m actually trying to accomplish is to show the results like this page:

If you hover over the first result / product, you will notice that you see all sizes for the color shown in the image. The same product has other coloroptions and if you hover over the other coloroptions, the sizes change according to the new color.


I’m trying to accomplish something like that and I’m trying to get everything on a single query.

I need two solutions. A SELECT to retrieve ALL products and another SELECT to retrieve ALL products THAT MATCHES FILTER REQUIREMENTS. My best solution so far to get ALL products (no filters added, no where clause) is like the statement in the post. If I use that selection statement without ‘Where Clause’ then I get this result.

Product_ID            Manufacturer            Product_Name            Color            Sizes            
----------------------------------------------------------------------------------------------
    1                    Nike               Air Max Talldress         Black           Medium, Large   
    1                    Nike               Air Max Talldress         Purple          Small
    2                   Adidas              High Voltage              Green           Medium   
    3                    Puma               Black Morris Shortdress   Yellow          Medium  
    4                    Nike               Jordani Sway              White           Large




Thats quite good. But as you notice, if I want it like the website I linked to, which I want, then some type of GROUP_CONCAT would also be needed on the color coulmn to “get it right from the start”. I tried to play around with it but I was unable to make it work.


Right now, I retrive the resultset like above, then I fix it in PHP. But it requires me to loop through the entire resultset, and then check every ID so I can match and “put togethor” the associated colors for that specific product, to get the same outcome as the website I linked to.


Then, my solution for the filter requirements was to use 'Where Clause as the example mentioned in my first post.

It worked perfectly for multiple filters, no problem until I noticed that it mess up the “sizes concat”, when we add size as a filter ( Size IN (‘’) )… Which in my case is not good.


A product comes in several colors and I want to show all colors separately when the user requests 'all products'. When you hover over the product you should also be able to see all the coloroptions for the specific product. And if you look at the website that I linked to for example, you notice that after hovering on the product you can also hover over the color options that appear and then the sizes for the specific color will be visible. Even if you filter for a specific color or size on their website you will have access to the other productoptions aswell.
I hope it became clearer now what I'm trying to accomplish. If there are any better solutions than the one I'm using right now, please feel free to enlighten me
Bless //Neo
7

Hope it makes more sense now :slight_smile: Check the post

8

This worked. But if a customer selects these filters for example (color = blue, red & yellow, And sizes = medium, small & large And lets say trademark = nike, adidas) I cant see how your solution would work with multiple selections. Pls correct me if im wrong :slight_smile:

At the moment if customer selects ‘color blue’ we just add it to ‘WHERE color IN(‘blue’)’, if he ALSO selects yellow for example We just add it in to color like this: 'WHERE color IN (‘blue’,‘yellow’). If he selects another filter lets say sizes small & large, we keep adding to the WHERE clause like this: ‘WHERE color IN (‘blue’,‘yellow’) AND size IN (‘small’,‘large’)’ and so on.

I think unfortunately your solution only works for one scenario and not for all of them, but as I mentioned earlier correct me if I’m wrong.

Take care mate :slight_smile:

9

Hmmm… You’ve changed the parameters a little.

You could do it this way (I think, not tested and it’s early in the morning). You might need a DISTINCT in there as well…

SELECT Products.Product_ID
      , Manufacturer
      , Product_Name
      , Colors
      , Sizes 
   FROM InStock 
  INNER JOIN Products ON Products.ID = Products.Product_ID 
  INNER JOIN (SELECT Product_ID
                   , GROUP_CONCAT(Color) AS Colors
                   , GROUP_CONCAT(Size) AS Sizes 
                FROM InStock 
               INNER JOIN JOIN Products ON Products.ID = Product_ID 
             GROUP BY Product_ID) SQ ON SQ.Product_ID = Products.Product_ID
  WHERE Color = 'Black' -- OR IN ('Black', 'Blue')
    AND Size = 'Medium' -- OR IN ('Small', 'Medium')
10

Good day or Good morning. :) Hope everything is good.

Let’s try a new examples. I notice that I have been a bit unclear and confusing.
These are my tables:


Products table:

ID               Manufacturer               Product_Name
-------------------------------------------------------------------------
1                 Nike                      Air Max Talldress  
2                 Adidas                    High Voltage 
3                 Puma                      Black Morris 
4                 Nike                      Jordani Sway

InStock table: ``` ID Product_ID Color Size Quantity

1 1 Black Medium 61
2 1 Black Large 26
3 1 Purple Small 37
4 1 Red XSmall 88
5 2 Green Medium 74
6 2 Red Large 121
7 2 White Small 42
8 3 Yellow Medium 101
9 4 White Large 22
10 4 White Small 41

<br>
On the website I want to present a "Record/Product Card/Result" for every color i have for A specific product, and within every "Record/Product Card/Result" I also need som additinal information about that specific product.

<b>Please check this page to understand what I want to achieve:</b>
https://www.zalando.se/damklader-cocktailklanningar/

( Hover over the first result/product card on the website ( Don't get stuck on the girls ;) ) and you see what other colors are available for that product, and then if you hover over one of those colors, you'll see which sizes are available for that color. If you choose to hover over another color instead, then you get new sizes according to the color you hover over. But everything is relevant to the same Product_ID of course. )
<br>
<b>Based on my two tables (Products-table & InStock-table), I need a resultset that looks like the table below. Then I can easily handle it in PHP without unnecessary operations that creates a huge mess.</b>
<br>

Product_ID Manufacturer Product_Name Color Colors Sizes(for value in ‘Color’-column)

1 Nike Air Max Talldress Black Black,Purple,Red Medium, Large
1 Nike Air Max Talldress Purple Black,Purple,Red Small
1 Nike Air Max Talldress Red Black,Purple,Red XSmall
2 Adidas High Voltage Green Green, Red, White Medium
2 Adidas High Voltage Red Green, Red, White Large
2 Adidas High Voltage White Green, Red, White Small
3 Puma Black Morris Yellow Yellow Medium
4 Nike Jordani Sway White White Small, Large


<br>
<b>Important:</b>

This has to work with multiple filters. Previously, we used 'WHERE' to filter. It returned the right result, but without the concatenation of all the other sizes.

So to make it clear. Even when the user adds filters, the ROWS/Records that we get back must look like the ROWS/Records in the resultset above. The number of results will of course decrease when the user adds filters, but the records/rows must have the same layout / structure as they have in the table/resultset above

<br>
Take care
//Neo
11

Did you try the query I posted in post #9?

12

I must have a new record/row for every color with the same ID(see the structure in my latest post to you)

I see that you use GROUP_CONCAT on colors on your example, that will return only one row/record with all colors in it.

Something like this:

Product_ID         Manufacturer        Product_Name               Colors                 Sizes

1                  Nike                Air Max Talldress          Black,Purple,Red       Medium, Large, Small, XSmall

The above is not outcome im looking for. This is the desired result in this case: 
Product_ID         Manufacturer        Product_Name        Color          Colors                 Sizes(for value in 'Color'-column)

1                  Nike                Air Max Talldress   Black          Black,Purple,Red       Medium, Large
1                  Nike                Air Max Talldress   Purple         Black,Purple,Red       Small
1                  Nike                Air Max Talldress   Red            Black,Purple,Red       XSmall
13

OK, so that means you’re going to have two different subqueries (unless @r937 can come up with a better way of handling this). Test thoroughly of course, especially since I might have typos in…

SELECT Products.Product_ID
      , Manufacturer
      , Product_Name
      , Instock.Color
      , AvailableColors
      , AvailableSizes 
   FROM InStock 
  INNER JOIN Products ON Products.ID = Products.Product_ID 
  INNER JOIN (SELECT Product_ID
                   , CONCAT(Color) AS AvailableColors
                FROM InStock 
               INNER JOIN JOIN Products ON Products.ID = Product_ID 
             GROUP BY Product_ID) SQC ON SQC.Product_ID = Products.Product_ID
  INNER JOIN (SELECT Product_ID
                   , Color
                   , GROUP_CONCAT(Size) AS AvailableSizes 
                FROM InStock 
               INNER JOIN JOIN Products ON Products.ID = Product_ID 
             GROUP BY Product_ID, Color) SQS ON SQS.Product_ID = Products.Product_ID AND SQS.Color = Instock.Color
  WHERE Instock.Color = 'Black' -- OR IN ('Black', 'Blue')
    AND Size = 'Medium' -- OR IN ('Small', 'Medium')
1 Like
14

You’ve definitely put me on the right track. I got it to work with the following lines.

SELECT InStock.Product_ID, Manufacturer, Product_Name,
InStock.Color, AllColors, Sizes FROM InStock

JOIN Products ON Products.ID = InStock.Product_ID

JOIN (SELECT Product_ID, GROUP_CONCAT(DISTINCT Color) AS 
AllColors FROM InStock 
GROUP BY Product_ID) newtable 
ON newtable.Product_ID = InStock.Product_ID

JOIN (SELECT Product_ID, Color, GROUP_CONCAT(Size) AS Sizes
FROM InStock 
GROUP BY Product_ID, Color) newtable2 
ON newtable2.Product_ID = InStock.Product_ID 
AND newtable2.Color = InStock.Color


GROUP BY Product_ID, Color

This works even with (WHERE Size = ‘medium’), it doesn’t remove the other concat-sizes from the record that is returned, because they are joined from other tables this time.

Thanks alot man

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.