Summarising data using sql

Question

I have logging entry table with the relevant following columns: eventype, userid. I can simply summarise the data using the following query to get a total for each user event type

SELECT     EventType, COUNT(EventUserID) AS Total, EventUserID
FROM         dbo.vSystemEventLog AS SE
GROUP BY EventType, EventUserID

However i need also to have running total for each individual eventtype regardless of user. How do i go about this?

Cheers

Answer 1

I think this might be what you want:

SELECT      IsNull(EventType,'SUMMARY') as [EventType],
            IsNull(EventUserId, 'TOTAL') as [EventUserId], 
            COUNT(EventUserID) AS [Total]

FROM    dbo.vSystemEventLog
GROUP BY EventType, EventUserID With Cube

I not entirely sure what you want - sample output and/or data would be most helpful.

However, I am guessing you need to group it the other way round so that the query groups by EventUserId first.

Ok - so I have created this test data & SQL. I think this is what you want.

Create Table #t
(
EventType int,
EventUserId int
)
Insert Into #t
Select 100, 18 union all Select 100, 18 union all Select 100, 18

Insert Into #t
Select 101, 16 union all Select 101, 16  union all Select 101, 16 
union all Select 101, 16 union all Select 101, 16  union all Select 101, 16 

Insert Into #t
Select 101, 18 union all Select 101, 18 union all Select 101, 18 union all Select 101, 18

Insert Into #t
Select 102, 18 union all Select 102, 18 


Select  IsNull(Convert(varchar(50), EventUserId), 'SUMMARY') As [EventUserId],
        IsNull(Convert(varchar(50), EventType), 'TOTAL') as [EventType],
        Count(EventUserId) as [Total]
From #t
Group By EventUserId, EventType with cube
Order by 1

drop table #t

This produces the following output: