joining 3 queries with outer join

Question

Hi, I have created a table tblOperationLog and wrote triggers to fill it as a user deletes, updates or inserts a row in my main table. It’s the delete and insert one:

CREATE TRIGGER FILL_TABLE  
ON Person FOR INSERT, DELETE
AS  

  INSERT INTO tblOperationLog  
 SELECT  SYSTEM_USER, 
         'user has inserted a row with ID = ' + Convert(nvarchar, inserted.id), 
          'Insert', CURRENT_TIMESTAMP, getdate()
  FROM inserted 


  INSERT INTO tblOperationLog  
 SELECT  SYSTEM_USER, 
         'user has deleted a row with ID = ' + Convert(nvarchar, deleted.id), 
          'Insert', CURRENT_TIMESTAMP, getdate() 
  FROM deleted 

Now I want to write a procedure to show the average of the operations that each user do in each day. I want to have the average of each operation( delete, insert and update) in the separate columns. To aim this goal I have written these queries:

select A.Users , avg(A.[Number Of Inserts])as 'Number Of Inserts' from
(select  Users,[Time],COUNT(*) as 'Number Of Inserts' from tblOperationLog where Opertion='Insert' group by Users, [Date]) A  group by Users

go
select B.Users , avg(B.[Number Of Updates])as 'Number Of Updates' from
(select  Users,[Date],COUNT(*) as 'Number Of Updates' from tblOperationLog where Operation='Update' group by Users, [Date]) B  group by Users

go

select C.Users , avg(C.[Number Of Deletes])as 'Number Of Deletes' from
(select  Users,[Date],COUNT(*) as 'Number Of Deletes' from tblOperationLog where Operation='Delete' group by Users, [Date]) C  group by Users

Go

with the above procedures I show each operation in a separate table. But I want to have them all in one table and I want to have for the users who don’t have delete or … operation 0 as average. Can you help me join them all. I think I have to use full outer join. But every time there is a error in my final query. I use SQL Server.

Answer 1

Look at the "UNION" SQL operator

Answer 2

Here's a query that may work.

WITH OpLogCTE AS (
    SELECT Users, Date, Operation, AVG(COUNT(*)) OVER (PARTITION BY Users, Date, Operation) AS OpCount
    FROM tblOperationLog
    GROUP BY Users, Date, Operation
    )

SELECT Users, Date,
    SUM(CASE Operation WHEN 'Insert' THEN OpCount ELSE 0 END) AS InsertCount,
    SUM(CASE Operation WHEN 'Update' THEN OpCount ELSE 0 END) AS UpdateCount,
    SUM(CASE Operation WHEN 'Delete' THEN OpCount ELSE 0 END) AS DeleteCount
FROM OpLogCTE
GROUP BY Users, Date
ORDER BY Users, Date

The top part is a Common Table Expression (CTE). It contains a query that gets the average values for each user, date and operation.

The lower part takes the results of the CTE and groups the data so that you get a row for each user and date.

Answer 3

Is this what you need? (Are you trying to calculate the average daily number of each operation for each user)

SELECT * 
INTO #tblOperationLog
FROM (
SELECT 'Alice' AS Users, CAST('2010-07-29' AS DATE) AS [DATE],
'Delete' AS OPERATION UNION ALL
SELECT 'Bob' AS Users, CAST('2010-07-30' AS DATE) AS [DATE],
'Insert' AS OPERATION UNION ALL
SELECT 'Bob' AS Users, CAST('2010-07-30' AS DATE) AS [DATE],
'Insert' AS OPERATION
) DemoData


DECLARE @DateCount float 
SELECT @DateCount= COUNT(DISTINCT [DATE]) FROM #tblOperationLog

SELECT Users,
COUNT(CASE WHEN OPERATION = 'Delete' THEN 1 END)/@DateCount AS DelAverage,
COUNT(CASE WHEN OPERATION = 'Insert' THEN 1 END)/@DateCount AS UpdateAverage,
COUNT(CASE WHEN OPERATION = 'Update' THEN 1 END)/@DateCount AS InsertAverage
FROM #tblOperationLog
GROUP BY Users

Output

Users DelAverage             UpdateAverage          InsertAverage
----- ---------------------- ---------------------- ----------------------
Alice 0.5                    0                      0
Bob   0                      1                      0