Select Distinct Records and count other columns related to record

I am trying to create a SQL Query wherein it will pick all distinct records from the first column and do a count of all the items related to that column similar to a countifs command in Excel. I am new to SQL and I am really lost at this point. please help.

Sample Table:

Employee	Item	Weight
EmpA	Red Item	Light
EmpB	Red Item	Heavy
EmpC	Red Item	Heavy
EmpA	Blue Item	Light
EmpB	Red Item	Light
EmpD	Blue Item	Light
EmpA	Red Item	Heavy
EmpC	Blue Item	Heavy
EmpA	Red Item	Light
EmpB	Red Item	Heavy
EmpD	Red Item	Light
EmpA	Blue Item	Light
EmpB	Red Item	Heavy
EmpC	Blue Item	Light
EmpA	Red Item	Light
EmpB	Red Item	Light
EmpD	Red Item	Light

Expected output:

Employee	RecordCount	Red Item	Blue Item	Light	Heavy
EmpA	6	4	2	5	1
EmpB	5	5	0	2	3
EmpC	3	1	2	1	2
EmpD	3	2	1	3	0

Answer

You would use conditional aggregation, which looks something like this:

select employee, count(*) as num_items,
       sum(item like 'Red%'),
       sum(item like 'Blue%'),
       sum(weight = 'Light'),
       sum(weight = 'Heavy')
from t
group by employee