Get data from GROUP BY clause where COUNT returns just 1-CodePudding

 ------- ----- 
| User  | Rol |
 ------- ----- 
| user1 | r1  |
| user1 | r2  |
| user1 | r3  |
| user3 | r1  |
| user3 | r5  |
| user4 | r4  |
| user5 | r2  |
 ------- -----

A group by / count would return:

select User, count(Rol) as 'RolCount' 
from 
  table 
group by User;

 ------- ---------- 
| User  | RolCount |
 ------- ----------    
| user1 |        3 |
| user3 |        2 |
| user4 |        1 |
| user5 |        1 |
 ------- ----------

Is it possible to show the rol name for the records having count(Rol) = 1 in the same sql statement? Like so:

 ------- ---------- ----- 
| User  | RolCount | Rol |
 ------- ---------- ----- 
| user4 |        1 | r4  |
| user5 |        1 | r2  |
 ------- ---------- -----

CodePudding user response：

WITH CTE(USERR,ROL)AS 
 (
    SELECT 'user1' , 'r1'  UNION ALL
    SELECT 'user1'  ,'r2'  UNION ALL
    SELECT 'user1'  ,'r3'   UNION ALL
    SELECT 'user3'  ,'r1'   UNION ALL
    SELECT 'user3'  ,'r5'   UNION ALL
    SELECT 'user4'  ,'r4'   UNION ALL
    SELECT 'user5'  ,'r2'
 )
 SELECT C.USERR,COUNT(C.USERR)CNTT,MAX(C.ROL)ROL
   FROM CTE AS C
    GROUP BY C.USERR
      HAVING COUNT(C.USERR)=1

Based on the above comment

CodePudding user response：

Here's an alternative way to do it:

with aggt(usr, count_rol)as
(
    select usr, count(rol)  from t group by usr
)
select usr
    , count_rol
    , iif(count_rol=1,(select top(1) ROL from t where t.usr=aggt.usr),'')
from aggt

I included top(1), but I tried it without, and that works as well.