CODEWITHSUNDEEP
mysql
user6437700
user6437700

Reputation: 199

Group by a field in my sql and concatinate another field values as one string

My table is something like that.

Location | Building | Department | 

L1        | B1       | D1      
L2        | B2       | D2      
L3        | B1       | D3

What I want to do is a query that counts locations and groups by buildings, but then display departments in that building as a string

Building | Count L | Departments | 

B1        | 2       | D1,D3     
B2        | 1       | D2

Upvotes: 0

Views: 68

Answers (2)

dwir182
dwir182

Reputation: 1549

You can use Group_Concat function

Definition:

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values

The query would be like:

Select 
   Building, 
   count(Location) as `Count L`, 
   group_concat(Department) as Departments
From
   your_table
Group By
   Building

Upvotes: 2

Gauravsa
Gauravsa

Reputation: 6528

select building, Count(location) as 'Count L', GROUP_CONCAT(COALESCE(Departments,'')) 
from yourtable
group by building

This will help with nulls also.

Upvotes: 0

Related Questions