How can you calculate the AVG, COUNT, SUM, and MIN of each row?

801 Asked by JackRussell in SQL Server , Asked on Nov 1, 2019

For calculating the operations on the row we will have to find max, AVG, COUNT, SUM, and MIN of marks then below will be operation:

CREATE TABLE [dbo].[STUDENT]

(

[STUDENTID] [int] NULL,

[STUDENTName] [varchar](30) NULL,

[SUBJECT1] decimal(10,2) NULL ,

[SUBJECT2] decimal(10,2) NULL ,

[SUBJECT3] decimal(10,2) NULL ,

[SUBJECT4] decimal(10,2) NULL ,

[SUBJECT5] decimal(10,2) NULL

)

insert into student values(1,'SUT1',67,78,34,90,45)

insert into student values(2,'SUT2',67,45,78,67,45)

insert into student values(3,'SUT3',76,45,45,46,80)

insert into student values(4,'SUT4',78,34,45,76,45)

insert into student values(5,'SUT5',45,78,80,78,34)

insert into student values(6,'SUT6',80,90,45,45,78)

insert into student values(7,'SUT7',67,67,45,67,45)

insert into student values(8,'SUT8',46,67,45,76,80)

insert into student values(9,'SUT9',69,76,45,78,34)

insert into student values(10,'SUT10',67,78,34,45,78)

insert into student values(11,'SUT11',90,45,78,76,45)

insert into student values(12,'SUT12',67,67,78,78,34)

insert into student values(13,'SUT13',46,67,45,89,86)

Using the function:

SELECT [STUDENTID],[STUDENTNAME],(SELECT MAX(C) FROM

(VALUES (SUBJECT1),(SUBJECT2),(SUBJECT3),(SUBJECT4),(SUBJECT5)) AS VALUE(C)) AS MAX_MARKS ,

(SELECT MIN(C) FROM

(VALUES (SUBJECT1),(SUBJECT2),(SUBJECT3),(SUBJECT4),(SUBJECT5)) AS VALUE(C)) AS MIN_MARKS ,

(SELECT AVG(C) FROM

(VALUES (SUBJECT1),(SUBJECT2),(SUBJECT3),(SUBJECT4),(SUBJECT5)) AS VALUE(C)) AS AVG_OR_PERCENTAGE_MARKS ,

(SELECT SUM(C) FROM

(VALUES (SUBJECT1),(SUBJECT2),(SUBJECT3),(SUBJECT4),(SUBJECT5)) AS VALUE(C)) AS TOTAL_MARKS ,

(SELECT COUNT(C) FROM

(VALUES (SUBJECT1),(SUBJECT2),(SUBJECT3),(SUBJECT4),(SUBJECT5)) AS VALUE(C)) AS TOTAL_SUBJECTS

FROM STUDENT

Your Answer