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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
atari2600Author Commented:
Fantastic! Just what I needed. Thank you :)
Grade A!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.