By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table UserTable (
Id integer not null,
Name varchar(12) not null
);
insert into UserTable values (1, 'A B');
insert into UserTable values (2, 'A C');
insert into UserTable values (3, 'A C A C');
insert into UserTable values (4, 'A C C');
insert into UserTable values (5, 'A C B');
insert into UserTable values (6, 'A C C');
insert into UserTable values (7, 'A C D');
insert into UserTable values (8, 'A C E');
insert into UserTable values (9, 'A C F');
create table LogTable (
LogId integer not null,
Username varchar(12) not null,
Event varchar(12) not null
);
insert into LogTable values (1, 'A C A C', 'Read');
insert into LogTable values (2, 'A C F', 'Write');
insert into LogTable values (3, 'A C F', 'Read');
insert into LogTable values (4, 'A C C', 'Update');
insert into LogTable values (5,'A C C', 'Read');
insert into LogTable values (6,'A C F', 'Read');
insert into LogTable values (7,'A C F', 'Update');
insert into LogTable values (7,'A C F', 'Write');
insert into LogTable values (7,'A C E','Update');
insert into LogTable values (7,'A C F', 'Delete');
insert into LogTable values (10,'A C B', 'Delete');
insert into LogTable values (11, 'A C F','Copy');
insert into LogTable values (12, 'A C B','Read');
insert into LogTable values (13, 'A C F','Update');
49 rows affected
SELECT UT.Id,UT.Name,
coalesce([LEARNING ACTIVITY],0),
coalesce([Chat],0),
coalesce([USER READ],0),
coalesce([USER DELETE],0)
FROM UserTable UT
LEFT JOIN
(
select UserId,
SUM(CASE
WHEN ActivityType = 'Videos' THEN 1
WHEN ActivityType = 'Text' THEN 1
WHEN ActivityType = 'Page' THEN 1
WHEN ActivityType = 'Image' THEN 1
ELSE 0
END) AS [LEARNING ACTIVITY],
SUM(CASE WHEN ActivityType = 'Chat' THEN 1 ELSE 0 END) AS [Chat]
from Activity
group by UserId
) AC
ON UT.Id = AC.UserId
LEFT JOIN
(
select Username,
SUM(CASE
WHEN Event = 'Read' THEN 1
ELSE 0 END
) AS [USER READ],
SUM(CASE
WHEN Event = 'Delete' THEN 1
ELSE 0 END
) AS [USER DELETE]
from LogTable
group by UserName
) LT
ON LT.Username = UT.Name
| Id | Name | (No column name) | (No column name) | (No column name) | (No column name) |
|---|---|---|---|---|---|
| 1 | A B | 2 | 0 | 0 | 0 |
| 2 | A C | 0 | 1 | 0 | 0 |
| 3 | A C A C | 0 | 1 | 1 | 0 |
| 4 | A C C | 2 | 0 | 2 | 0 |
| 5 | A C B | 0 | 2 | 1 | 1 |
| 6 | A C C | 1 | 0 | 2 | 0 |
| 7 | A C D | 3 | 0 | 0 | 0 |
| 8 | A C E | 1 | 1 | 0 | 0 |
| 9 | A C F | 1 | 0 | 3 | 1 |