0

So, I have a database for logs and statistics. But, the amount of data sent is too high, so I was thinking of grouping the data into another table. So the structure of the original Database is:

ID  int
problem varchar(250)
channel_number  int
username varchar(50)
ip_address  nvarchar(50)
buffer_error_type varchar(50)
source int
Is_On   bit
logtype int
viewing_channel nvarchar(20)
playtime    int
date    bigint 

where the field problem has values such as: "The User cannot connect to server","User's categories are 0","User's channels are 0","Buffer Error"(here Buffer Error is specified by the field buffer_error_type as Warning or Error) etc. In the second table I would like to perform a insert select statement, which counts every single value of the field problem + specify the Buffer_Error value as Error,ignoring the Warning. So,the second table should have this structure:

username    varchar(50)
monthSel    bigint
CategoriesZero  int
ChannelsZero    int
BufferError int
BlackScreen int 
OtherLogs   int
id  int

I do not know if something like this is possible, however, I tried this query:

insert into LogsArchive.dbo.ProblemsArchive (username,monthSel,CategoriesZero,ChannelsZero,BufferError,BlackScreen,OtherLogs)
(select (username),25114721, count(distinct problem),count(distinct problem),count(distinct problem),
count(distinct problem),count(distinct problem) from DbLogs.dbo.Logs
where username in (select distinct username from DbLogs.dbo.Logs) 
 group by problem,username)

But I know I can't achieve the result with this.

1

Try using

SUM(Case WHEN problem Like '%err1%' THEN 1 ELSE 0 END) //for problem count

'%err1%' is a sample error containing letter to rectify which error is this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.