SQL Server 2008 R2 Subquery Grouping, Summing, and Counting

Question

I have a table in the database:

CREATE TABLE dbo.evals (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [agent_id] [int] NOT NULL,
    [matrix_1] [int],
    [matrix_2] [int],
    [matrix_2] [int])

Each of the matrix_ (x) columns has a default value of 0. When agents are evaluated by managers, it is not required that a record be made for each matrix. Agents can be evaluated daily. If a matrix is ​​entered, it will have a value from 1 to 5. If not, then 0. I need to create a report that sums and averages each matrix for each agent. I need not to count the value 0 when calculating the average value, so I need to somehow get a counter where the matrix value <> 0 for each of them. The report is not for one agent at a time, but for all agents in one report. I tried the general agent_id group with subqueries to get a matrix counter with a matrix <> 0, and it does not work. What I want in the end is something like:

select agent_id, sum(matrix_1) / (count(matrix_1) where matrix_1 <> 0), 
          sum(matrix_2) / (count(matrix_2) where matrix_2 <> 0),
          sum(matrix_3) / (count(matrix_3) where matrix_3 <> 0)
group by agent_id

-, . , , .

Answer 1

! . , float Avg:

select
    agent_id,
    sum(matrix_1) Matrix1Sum,
    avg(case when matrix_1 > 0 then cast(matrix_1 as float) end) Matrix1Average,
    sum(matrix_2) Matrix2Sum,
    avg(case when matrix_2 > 0 then cast(matrix_2 as float) end) Matrix2Average,
    sum(matrix_3) Matrix3Sum,
    avg(case when matrix_3 > 0 then cast(matrix_3 as float) end) Matrix3Average
from evals
group by
    agent_id