CODEWITHSUNDEEP
mysqlsqlgroup-byhaving
minesh..
minesh..

Reputation: 155

group by having clause query

Customer table : (Id, FirstName, LastName, City, Country, Phone)

** List the number of customers in each country. Only include countries with more than 1 customers. --> query will be as follows 

 SELECT COUNT(Id), Country
    FROM Customer
    GROUP BY Country
    HAVING COUNT(Id) > 1

Results: 3 records
Count   Country
2   France
4   Germany
3   USA

Problem : I need to get names of these count ie. FirstName in the same query EG : as below 

Results: 3 records
    Count   Names             Country
    2       john,max          France
    4       abc,xyz,aab,cdf   Germany
    3       mmm,fmf,dm        USA

Is it possible?

Upvotes: 0

Views: 137

Answers (1)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

Try this:

select count(id) as count,
       group_concat(first_name),
       country
from Customer
group by country

Group Concat function returns a string with concatenated non-NULL value from a group.

Upvotes: 2

Related Questions