Cannot display extra attributes in my query request

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0