Using COUNT in GROUP_CONCAT

Question

This is my table:

  id  | fk_company
-------------------
  1   |     2    
  2   |     2    
  3   |     2    
  4   |     4    
  5   |     4    
  6   |     11   
  7   |     11   
  8   |     11   
  9   |     12

The result I want should be string "3, 2, 3, 1" (count of items that belong to each company), because this is just part of my complex query string.

I tried to use this query:

SELECT GROUP_CONCAT(COUNT(id) SEPARATOR ", ")
FROM `table` GROUP BY fk_company;

But I got an error:

Error Number: 1111
Invalid use of group function

I have a feeling COUNT, MAX, MIN or SUM can't be used in GROUP_CONCAT. If so, do you know another way to do this?

Why do you want this? What happens next?

Strawberry
– Strawberry

2015-02-27 18:26:54 +00:00
Commented Feb 27, 2015 at 18:26 — Strawberry
– Strawberry, Commented Feb 27, 2015 at 18:26

informatik01 · Accepted Answer · 2021-01-06 23:22:43Z

23

You need to COUNT() with GROUP BY in an inner SELECT clause first and then apply GROUP_CONCAT();

SELECT GROUP_CONCAT(cnt) cnt
FROM (
    SELECT COUNT(*) cnt
    FROM table1
    GROUP BY fk_company
) q;

Output:

|   CNT   |
-----------
| 3,2,3,1 |

Here is SQLFiddle demo.

edited Jan 6, 2021 at 23:22

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Jul 4, 2013 at 6:27

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mesnicka Over a year ago

Thank you very much, I spent a lot of time to fix this. Unfortunately I don't have enough time to play so I needed help even if this result looks so easy.

Strawberry Over a year ago

Note that this presently relies on the fact that fk_company numbers are arranged sequentially, in a manner that corresponds to id

Inc33 · Accepted Answer · 2020-08-08 05:09:52Z

3

You can also achieve that by counting the number of commas (or whatever's your separator) in the GROUP_CONCAT:

SELECT (LENGTH(GROUP_CONCAT(DISTINCT fk_company))-LENGTH(REPLACE(GROUP_CONCAT(DISTINCT fk_company), ',', '')))
FROM `table`
GROUP BY fk_company

answered Aug 8, 2020 at 5:09

Inc33

1,9511 gold badge24 silver badges27 bronze badges

Comments

Naveen Kumar Alone · Accepted Answer · 2013-07-04 12:51:20Z

0

select GROUP_CONCAT(counts) 
   from (
      select count(id) counts from
          table group by fk_company
   );

edited Jul 4, 2013 at 12:51

answered Jul 4, 2013 at 6:26

Naveen Kumar Alone

7,6985 gold badges40 silver badges59 bronze badges

Comments

Mudit Gupta · Accepted Answer · 2018-10-16 07:23:18Z

-1

SELECT A,  
GROUP_CONCAT(B SEPARATOR '/') AS 'Category',  
GROUP_CONCAT(C SEPARATOR '/') AS 'ALIAS_NAME',COUNT('ALIAS_NAME') AS 'Count'  
FROM carnews  
...  
...
GROUP BY 1
ORDER BY 4 DESC

This works well in my case.

answered Oct 16, 2018 at 7:23

Mudit Gupta

4515 silver badges8 bronze badges

Collectives™ on Stack Overflow

Using COUNT in GROUP_CONCAT

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related