I have a table similar to below

From this table, I am trying to create a View as below

I am not able to figure out how to group the table to achieve this view. Any help would be greatly appreciated

CodePudding user response：

Here's one option:

Sample data (you have it already, so you don't type that):

SQL> with test (insurance_id, created_by, closed_by) as
  2    (select 1, 'user a', 'user a' from dual union all
  3     select 2, 'user a', 'user c' from dual union all
  4     select 3, 'user b', 'user c' from dual union all
  5     select 4, 'user b', 'user c' from dual union all
  6     select 5, 'user b', 'user c' from dual
  7    ),

Query begins here:

  8  all_users as
  9    (select created_by username from test
 10     union
 11     select closed_by  from test
 12    )
 13  select u.username,
 14    sum(case when t.created_by = u.username then 1 else 0 end) total_created,
 15    sum(case when t.closed_by  = u.username then 1 else 0 end) total_closed
 16  from all_users u cross join test t
 17  group by u.username
 18  order by u.username;

USERNA TOTAL_CREATED TOTAL_CLOSED
------ ------------- ------------
user a             2            1
user b             3            0
user c             0            4

SQL>

CodePudding user response：

I would be inclined to have a separate Users table and only have an integer of UserId in the main table. CROSS APPLY should avoid reading the same table twice.

SELECT X.UserName
    ,SUM(CASE WHEN X.Activity = 'Created' THEN 1 ELSE 0 END) AS TotalCreated
    ,SUM(CASE WHEN X.Activity = 'Closed' THEN 1 ELSE 0 END) AS TotalClosed
FROM YourTable T
    CROSS APPLY
    (
        VALUES (T.CreatedBy, 'Created')
            ,(T.ClosedBy, 'Closed')
    ) X (UserName, Activity)
GROUP BY X.UserName
ORDER BY UserName;

CodePudding user response：

create table sometable (user_id, created_by,closed_by)
as
select 1,  'user a', 'user a' from dual union all
select 2,  'user a', 'user c' from dual union all
select 3,  'user b', 'user c' from dual union all
select 4,  'user b', 'user c' from dual union all
select 5,  'user b', 'user c'  from dual;


SELECT   *
FROM     sometable
UNPIVOT  (  username
     FOR col IN ( created_by
                , closed_by
            )
     )
PIVOT   (  COUNT (user_id)
     FOR col IN     ( 'CREATED_BY'  AS total_created
                  , 'CLOSED_BY'    AS total_closed
            )
     )
ORDER BY username
;

USERNAME    TOTAL_CREATED    TOTAL_CLOSED
user a    2    1
user b    3    0
user c    0    4