1

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:

enter image description here

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!

  • 2
    Show some source data as INSERT INTO and final result for this data. – Akina May 29 at 10:00
  • 1
    For your "plain case" - try to use simple SELECT AVG(Biology + Maths + Geography)/3 FROM Students; – Akina May 29 at 10:02
  • @akina, I have updated the script with the insert of 3 exemplary rows of data and the .csv result – query_question May 29 at 10:32
  • If the output of your current query is one which you need - the query is optimal. Another question is that your data is denormalized - and this is what needs to be corrected. – Akina May 29 at 10:37
  • @akina, yes, data is not in 3NF indeed as it is just exemplary. I have tried your query, it retrieves a single result 2.22 (it retrieves the three fields' total amounts and divides by three - which is equivalent to the average mark of a student in the database. The output I target is actually a new column which includes the average mark of the three marks for each row/student instead for the whole column. – query_question May 29 at 10:44
1

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.

| improve this answer | |

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.