How to do Group By and SELF JOIN in single table-CodePudding

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

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.

CodePudding user response：

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

CodePudding user response：

First of all thanks for asking question with such detail information.

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