How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?

I'm running MySQL 5.0.88 (Coldfusion8)

I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of distinct sizes/colors, so I can update my search criteria along with displaying the results.

However my GROUP_CONCAT does not return all expected values. I don't know what I'm missing, but it seems it is only returning Distinct min/max sizes/colors versus returning all Distinct sizes/colors across the recordset.

My product table:

CREATE TABLE dummy (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`iln` VARCHAR(13) NULL DEFAULT NULL,
`ean` VARCHAR(35) NULL DEFAULT NULL,
`artikelnummer` VARCHAR(35) NULL DEFAULT NULL,
`groesse` VARCHAR(10) NULL DEFAULT NULL,
`farbe` VARCHAR(35) NULL DEFAULT NULL,
`farbnummer` VARCHAR(10) NULL DEFAULT NULL,
`preis_ek` DECIMAL(12,2) NULL DEFAULT NULL,
`preis_vk` DECIMAL(12,2) NULL DEFAULT NULL,
`preis_aktuell` DECIMAL(12,2) NULL DEFAULT NULL,
`firma` VARCHAR(35) NULL DEFAULT NULL,
`marke` VARCHAR(35) NULL DEFAULT NULL,
`nos` VARCHAR(4) NULL DEFAULT NULL,
`nos_anzeige` VARCHAR(4) NULL DEFAULT NULL,
`aktiv` VARCHAR(4) NULL DEFAULT NULL,
`modus` VARCHAR(4) NULL DEFAULT NULL,
`bestand` DECIMAL(10,0) NULL DEFAULT '0'
    )

Products are listed by EAN, so for example a Shirt in Size S,M,L,XL would have 4 entries like so:

style      ean           size       price      qty
  123      111111111111    S          9.99       12
  123      111111111112    M          9.99        1
  123      111111111113    L          9.99       23
  123      111111111114    XL         9.99        0

Here is my Query:

```
SELECT COUNT(recordcount) AS total_records
, MIN(min_price_ek) AS ek_min
, MAX(max_price_ek) AS ek_max
, MIN(min_price_vk) AS vk_min
, MAX(max_price_vk) AS vk_max
, MAX(max_reb) AS rb_max
, SUBSTRING_INDEX( GROUP_CONCAT( DISTINCT sizeRange ), ',', 10 ) AS sz_rng
, SUBSTRING_IN

UPDATE
Now I see your error. The inner query uses aggregation, and takes out sizeRange which is not a column you aggregate on. So you only get "samples" of that column. Strictly speaking, your query is not valid SQL but MySQL allows it given a relaxed sql_mode.

So your query is inherently erroneous. Will see if I can help fix it.

ORIGINAL answer

My guess for you would be to check the value of group_concat_max_len.

It is by default just 1024, though you typically don't really want a limit for that.

The problem might be that you are only getting partial results, where, by chance or by order of evaluation, "S" and "XL" occupy first 1024 characters or more. I see no reason why "M" or "L" would not be there -- the GROUP_CONCAT doesn't do such distinctions.

So, try out:

SET group_concat_max_len := 1000000;

And execute your query again. If this works, make sure to set said param in you MySQL configuration file.

You may find my related post useful.

StackExchange Database Administrators Q#23431, answer score: 3