Count distinct values of a column and insert them to another table

Question

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.

Answer 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.