4

In a query like this:

SELECT GROUP_CONCAT(DISTINCT( p.products_id )) AS comma_separated 
FROM   products p 
       left join specials s 
              ON p.products_id = s.products_id 
       left join products_to_categories p2c 
              ON p.products_id = p2c.products_id 
       left join products_description pd 
              ON p.products_id = pd.products_id 
       inner join products_attributes pa 
               ON p.products_id = pa.products_id 
WHERE  p.products_status = '1' 
       AND p2c.categories_id = 57 
       AND Date_sub(Curdate(), interval 7000 day) <= p.products_date_added 
ORDER  BY p.products_id DESC

I am getting a result of long list of comma separated numbers in a field named comma_separated. How can I modify the query to count how many comma separated values there are?

EDIT:

Thanks, Barmar for the answer below. I kept on getting a much shorter list of numbers (107 values instead of 4000+ in the actual count), and now I finally found the cause. It seems GROUP_CONCAT is limited to 1024 characters, which is quite bad for my application. Source: http://www.bennadel.com/blog/2669-mysql-group-concat-fails-silently-when-it-hits-its-size-limit.htm

Nikita 웃
  • 2,042
  • 20
  • 45

1 Answers1

5

Use COUNT to count them.

SELECT Group_concat(DISTINCT( p.products_id )) AS comma_separated,
       COUNT(DISTINCT p.products_id) AS product_count
FROM ...
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks Barmar, I kept on getting 107 comma separated values while the count showed there should be more than 4,000. I just found the cause and updated my question with the details. – Nikita 웃 Dec 28 '15 at 08:29
  • 2
    You can increase `GROUP_CONCAT_MAX_LEN`. – Barmar Dec 28 '15 at 08:30
  • just found the answer :) http://stackoverflow.com/questions/26553823/what-is-the-maximum-allowance-for-group-concat-max-len-in-mysql – Nikita 웃 Dec 28 '15 at 08:33