MYSQL: GROUP BY Statement

[pechar]

Hi all,

We have a product table in the database. One of the columns is ProductGroup which we use in the case when a product is available in multiple colours say a red, green or blue cap. So if a product has multiple colours we add each product to the table but also specify its group.

If the item doesn't have different colours we dont specify a group. Unfortunately we cannot modify the database the default value of the productGroup column is an empty string (actually it is a Foxpro database but my question is more of a SQL statement problem)

I need to list all products those with a ProductGroup and those without a ProductGroup. Now my problem is I need those which have a ProductGroup to only show the first record. GROUP BY does this but does not return the other rows as unique groups, since it automatically groups the products with no ProductGroup.

Can anyone guide me into how I could get all the products and only the first product of the grouped ones. Hope I'm clear enough

Thanks

[ronverdonk]

Something like this will do it (I hope)

SELECT * FROM table_name WHERE color_group>'' GROUP BY color_group
  UNION
SELECT * FROM table_name WHERE color_group='' GROUP BY color;

Ronald

[pechar]

Something like this will do it (I hope)

SELECT * FROM table_name WHERE color_group>'' GROUP BY color_group
  UNION
SELECT * FROM table_name WHERE color_group='' GROUP BY color;

Ronald

I did as you told me and used the following where alt_code is the productgroup:

SELECT * FROM stocks WHERE alt_code >'' GROUP BY alt_code  
UNION
SELECT * FROM stocks WHERE alt_code='' GROUP BY alt_code

But the result is still the same. I get grouped products with a product group (as i want it) but also a group of the remaining products with no product group specified. I want these to be displayed not in a group but as single records (since these are all unique products)

To make is simpler what I need is e.g a cap in a group since I can have multicoloured, multisized caps. and a watch (which has no group) as a single product. In my case I have around 5000 products of which 500 are in a group so the result should give me something above 4500 say 4600 rows.

Thanks

[ronverdonk]

You have NOT implemented what I advised you to do, i.e.

The first set is grouped by the product group.
The second set is grouped by the color

the code you use groups everything by color group (alt_code). I'll show my code again

SELECT * FROM table_name WHERE color_group>'' GROUP BY [B]color_group[/B]
  UNION
SELECT * FROM table_name WHERE color_group='' GROUP BY [B]color[/B];

Ronald

[pechar]

You have NOT implemented what I advised you to do, i.e.

The first set is grouped by the product group.
The second set is grouped by the color

the code you use groups everything by color group (alt_code). I'll show my code again

SELECT * FROM table_name WHERE color_group>'' GROUP BY [B]color_group[/B]
  UNION
SELECT * FROM table_name WHERE color_group='' GROUP BY [B]color[/B];

Ronald

Oh sorry Ronald,

I missed that. But thanks a lot for the help much appreciated. Problem solved like this :)

Lukas

[ronverdonk]

Glad to be of help. See you around next time.

Ronald