How do I use COUNT in a GROUP_CONCAT and display in the MySQL output?

Here is the code I am using:

SELECT user.num                                          as 'User No',   
(SELECT GROUP_CONCAT(tableName SEPARATOR ', ') FROM 
         `<?php echo $TABLE_PREFIX;?>_accesslist` 
	  WHERE userNum = user.num 
	        AND accessLevel != 0)                     as 'Access List' 
          
FROM `<?php echo $TABLE_PREFIX ?>accounts` user    
WHERE true   

Open in new window


I want to count the number of records returned in the GROUP_CONCAT, and then output that total with the other information.

Any ideas? Thanks!
atari2600Asked:
Who is Participating?
 
nemws1Database AdministratorCommented:
You can't do a separate COUNT() column in a subselect the way you're doing it, but you CAN do something like this:  (include the total count in a single field along with the GROUP_CONCAT() data)  
SELECT user.num                                          as 'User No',   
(SELECT CONCAT(COUNT(*), " -- ", GROUP_CONCAT(tableName SEPARATOR ', ')) FROM 
         `<?php echo $TABLE_PREFIX;?>_accesslist` 
          WHERE userNum = user.num 
                AND accessLevel != 0)                     as 'Access List' 
          
FROM `<?php echo $TABLE_PREFIX ?>accounts` user    
WHERE true

Open in new window

You'll then need to use a little PHP magic to separate the COUNT() from the GROUP_CONCAT data.  A quick explode() would probably be the best idea.
0
 
skullnobrainsCommented:
you cannot count the entries in the group_concat using count. you need string functions for that

if you just need the number, you probably would be better of using a "group by" clause and a regular "count(*)".

it is pretty unclear to me what you are really trying to achieve but i guess this should be similar to what you need :

select user.num,count(user),group_concat(accesslist.FIELD_THAT_HOLDS_THE_LIST)
from users
inner join accesslist on accesslist.userID = accounts.userID
group by userID
0
 
atari2600Author Commented:
Hi skullnobrains,

I was wanting to see a total for a quick reference to know how many entries had been allocated to an user.

I need more than just the number, and the code I show is a stripped down version for brevity.

Thanks for your input.
0
 
atari2600Author Commented:
Fantastic! Just what I needed. Thank you :)
Grade A!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.