T-SQL query - how to AVG() the SUM() of columns' rows to a new row column? What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.

How should the query look like to achieve this, since the AVG function is an aggregate one that is retrieving the vertical average column-wise but not the horizontal. Is there an elegant way to achieve this by a query?

Could you please elaborate the below query on the screenshot or modify it so that it works? Is there an elegant way to make this using AVG and COUNT() only without the trivial sum/count?

I have the following solution on the screenshot to extend more so that it does the job but for me it seems not to be correct:



Please find the below code (my trivial solution as a sum and division by the number of the summed fields):


USE [Students]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Students](
 [StudentID] [int] NOT NULL,
 [StudentName] [nchar](100) NOT NULL,
 [StudentSurname] [nchar](100) NOT NULL,
 [Biology] [decimal](3, 2) NOT NULL,
 [Maths] [decimal](3, 2) NOT NULL,
 [Geography] [decimal](3, 2) NOT NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
 [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (1, N'Mike                                                                                                ', N'Manson                                                                                              ', CAST(2.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(1.00 AS Decimal(3, 2)))
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (2, N'Timo                                                                                                ', N'Torn                                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (3, N'Jeffrey                                                                                             ', N'Jones                                                                                               ', CAST(1.00 AS Decimal(3, 2)), CAST(3.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO

SELECT * FROM Students;

SELECT
(SELECT Biology + Maths + Geography)/3 AS Mark_average
FROM Students;


Here is the output of the above query:

Mark_average
1.666666
2.333333
2.666666


Is there an alternative and more elegant/sophisticated way (similar to what is on the screenshot, by e.g. a subquery using AVG() of SUM() function) to retrieve the result?

Thanks much in advance!
If you are looking for a different solution, you could always unpivot the data, aggregate and then join back on the source table.  Here is an example.

Also, thank you so much for including sample table and data to work with, so much easier.

;WITH CTE_Unpivot AS
    (
    SELECT StudentID, MarkSubject, Mark 
    FROM dbo.Students AS S
        UNPIVOT (Mark FOR MarkSubject IN (Biology, Maths, [Geography])) AS upvt
    )
, CTE_Average AS
    (
    SELECT StudentID, AVG(Mark) AS StudentAverage
    FROM CTE_Unpivot AS U
    GROUP BY StudentID 
    )
SELECT S.StudentID
    , S.StudentName
    , S.StudentSurname
    , S.Biology
    , S.Maths
    , S.[Geography]
    , A.StudentAverage
FROM dbo.Students AS S
    LEFT OUTER JOIN CTE_Average AS A ON S.StudentID = A.StudentID


I feel the need to point out that this is less efficient than just adding the fields together and dividing them.  Manipulating the data like this touches it multiple times, which means that it is more likley to spill to tempdb and have a higher cpu cost.  Probably negligible, but still.