'How to do Group By and SELF JOIN in single table

Here is what I am trying to get done. Partly I am thru, Partly I am stuck, and need some help.

Table structure:

CREATE TABLE UserRole
(
    Id bigint NOT NULL AUTO_INCREMENT,
    Name varchar(100) NOT NULL,
    Description varchar(200) NULL,
    IsEnabled bit NOT NULL, -- 1 if a role is enabled, 0 otherwise
    Created date NOT NULL, -- When a role was created
    CreatedBy varchar(200) NOT NULL, -- Who created a role
    Updated date NULL, -- When a role was updated (if at all)
    UpdatedBy varchar(200) NULL, -- Who updated a role (if at all)
    CONSTRAINT PK_UserRole PRIMARY KEY ( Id ASC )
)

Data:

Id Name Description IsEnabled Created CreatedBy Updated UpdatedBy
1 Role_1 NULL 1 2020-04-15 Admin NULL NULL
2 Role_2 Description 1 2020-04-16 ADMIN 2020-04-17 John Smith
3 Role_3 Description 0 2020-04-16 John SMITH 2020-04-17 Ben SMITH
4 Role_4 Description 1 2020-04-19 bEn SmiTh 2020-04-21 BEN SMITH

Expected result and my working on getting the report:

To get UserName, NoOFCreatedRoles, NoOfCreatedEnabled and NoOfUpdated.

For the above input here is the sample output

enter image description here

A few assumptions:

  • You can assume there is at least one role created by each user.

  • Your query should work on MySQL 8.0 since I am using that and that is what is expected.

  • Query should not return NULL for numerical columns, instead of NULL get it replaced with -1.

My solution

-- Collect NoOfCreatedRoles ( Working )
select  UCASE(CreatedBy), count(*) as 'NoOfCreatedRoles' 
     from UserRole 
     Group By UCASE(CreatedBy) 
     order by  UCASE(CreatedBy) desc;

Result

UCASE(CreatedBy) NoOfCreatedRoles
JOHN SMITH 1
BEN SMITH 1
ADMIN 2 
-- Collect NoOfUpdatedRoles
select  TRIM(UCASE(UpdatedBy)), count(*) as 'NoOfUpdatedRoles' 
     from UserRole 
     Group By TRIM(UCASE(UpdatedBy))  desc;
TRIM(UCASE(UpdatedBy)) NoOfUpdatedRoles
JOHN SMITH 1
BEN SMITH 2
NULL 1

NULL should be removed, why is it coming ????

-- Joined Query
select  distinct UCASE(a.CreatedBy) as 'UserName' , 
        count(*) as 'NoOfCreatedRoles'  , 
        count(*) as 'NoOfUpdatedRoles' from UserRole a
    join UserRole b 
    where TRIM(UCASE(a.CreatedBy)) = TRIM(UCASE(b.CreatedBy))
    Group By UCASE(a.CreatedBy), UCASE(a.UpdatedBy) 
    order by UCASE(a.CreatedBy) desc;
UserName NoOfCreatedRoles NoOfUpdatedRoles
JOHN SMITH 1 1
BEN SMITH 1 1 (Wrong, should be 2)
ADMIN 2 2 (Wrong, should be -1)

Also, why am I getting the wrong result and how do I get numberOfCreatedAndEnabled column, need some help with some insightful descriptions would be greatly appreciated.

sqlgroup-byself-join


Solution 1:[1]

Count first, join the results

select t1.usr, NoOfCreatedRoles, 
        case NoOfCreatedEnabled when 0 then -1 else NoOfCreatedEnabled end NoOfCreatedEnabled,
        coalesce(NoOfUpdatedRoles, -1) NoOfUpdatedRoles
from (
     select TRIM(UCASE(CreatedBy)) usr, count(*) as NoOfCreatedRoles ,
     sum(IsEnabled ) as NoOfCreatedEnabled
     from UserRole ur
     Group By TRIM(UCASE(CreatedBy))
) t1 left join (
     select TRIM(UCASE(UpdatedBy)) usr, count(*) as NoOfUpdatedRoles
     from UserRole 
     Group By  TRIM(UCASE(UpdatedBy))
) t2 on t1.usr = t2.usr

Solution 2:[2]

Since IsEnabled column is 1 for all the enabled roles simply sum() will calculate NoOfCreatedEnabled. To calculate NoOfUpdatedRoles you can use subquery. Combined query will be as below:

Query 1:

  select  UCASE(CreatedBy) as 'UserName', count(*) as 'NoOfCreatedRoles',
      sum(IsEnabled ) as 'NoOfCreatedEnabled ',
      (select count(*) from UserRole u where UCASE(u.UpdatedBy) = UCASE(ur.CreatedBy)  ) as 'NoOfUpdatedRoles'
  from UserRole ur
  Group By ur.CreatedBy
  order by UCASE(CreatedBy) desc;

Output:

UserName NoOfCreatedRoles NoOfCreatedEnabled NoOfUpdatedRoles
JOHN SMITH 1 0 1
BEN SMITH 1 1 2
ADMIN 2 2 0

Query 2 (replacing 0 with -1):

  select  UCASE(CreatedBy) as 'UserName', count(*) as 'NoOfCreatedRoles' ,
      sum(IsEnabled ) as 'NoOfCreatedEnabled ',
      (select COALESCE(NULLIF(count(*),0),-1) from UserRole u where UCASE(u.UpdatedBy) = UCASE(ur.CreatedBy)  ) as 'NoOfUpdatedRoles'
  from UserRole ur
  Group By ur.CreatedBy
  order by UCASE(CreatedBy) desc;

nullif(parameter1,0) will return null if parameter1 is same as second parameter which is 0 here. coalesce(parameter1,-1) will return -1 only if parameter1 is null

Output:

UserName NoOfCreatedRoles NoOfCreatedEnabled NoOfUpdatedRoles
JOHN SMITH 1 0 1
BEN SMITH 1 1 2
ADMIN 2 2 -1

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Serg
Solution 2 philipxy

Related Questions