asked on

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!

skullnobrains

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

atari2600

ASKER

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.

ASKER CERTIFIED SOLUTION

Nem Schlecht

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

atari2600

ASKER

Fantastic! Just what I needed. Thank you :)
Grade A!