This is my little Salesman table I am working on:

 ------- -------------- -------- 
| empId | name         | gender |
 ------- -------------- -------- 
|     3 | Suresh Raina | M      |
|     5 | Andrew       | M      |
|     7 | Moeen        | F      |
|    11 | Moeen        | M      |
 ------- -------------- -------- 

What I have to display in my query is the following:

name       count
Andrew       1
Moeen        2 (7,Moeen,F)(11,Moeen,M)
Suresh Raina 1

If count has more than one matching name then it displays all the attributes.

I have the following query but it is not printing the ones that have a count higher than 1.

select all  name, count( name) AS totalNumber 
  from Salesman GROUP BY name HAVING COUNT(name)= 1 OR COUNT(name)>1 IN 
   ( select group_concat(empId ,name , gender) from Salesman group by name ) order by name asc;

Any hints or ideas you can share would be appreciated.

CodePudding user response：

Try this.

Query

select
  name,
  count(*) as count,
  case when count(*) < 2 then ''
  else
  concat('(',
    group_concat(
      concat_ws(',', empid, name, gender)
      separator '),('
    ),
    ')'
  )
  end as flds
from salesman
group by name

Result

name         | count | flds                    
:----------- | ----: | :-----------------------
Andrew       |     1 |                         
Moeen        |     2 | (7,Moeen,F),(11,Moeen,M)
Suresh Raina |     1 |                         

Example

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=9a804f0ac1a7290af61ce3649670358c

Explanation

Start with this:

select name, count(*) as counter
from salesman
group by name

This gives you name and count. That's all good.

Now, you want to combine the fields. So, you could use concat_ws. The first parameter to that function will be the separator. We'll use ,. Then will be the list of fields.

So, concat_ws(',', name, gender) will return John,M or whatever the name in each row is. We'll take advantage of that.

Since we want to use concat_ws along with count(*), we need to do some kind of summarization of concat_ws. We do that with group_concat.

select name, count(*) as counter, group_concat(concat_ws(',', empid, name, gender))
from salesman
group by name

gives us

name         | counter | group_concat(concat_ws(',', empid, name, gender))
:----------- | ------: | :------------------------------------------------
Andrew       |       1 | 5,Andrew,M                                       
Moeen        |       2 | 7,Moeen,F,11,Moeen,M                             
Suresh Raina |       1 | 1,Suresh Raina,M                                 

Alright, we are closer. Now, we give group_concat a separator. That separates the records. We use the separate ),( so that the query becomes like this:

select name, count(*) as counter,
  group_concat(concat_ws(',', empid, name, gender) separator '),(')
from salesman
group by name

gives

> <pre>
> name         | counter | group_concat(concat_ws(',', empid, name, gender) separator '),(')
> :----------- | ------: | :----------------------------------------------------------------
> Andrew       |       1 | 5,Andrew,M                                                       
> Moeen        |       2 | 7,Moeen,F),(11,Moeen,M                                           
> Suresh Raina |       1 | 1,Suresh Raina,M                                                 
> </pre>

Even closer. All we have left is to add open and closed parenthesis around our group concat. Once we do that, we get a clean output.

However, we don't want Andrew and Suresh's information to be published because their count is 1. So, we put a case when ... else ... end statement.