Query doesn't work with AVG function, how to use AVG in SQL?


I am trying to run the below query which works fine without AVG function, but when using AVG it doesn't work, here is the query

 SELECT   [std].[std_id], 
          [std].[std_name], 
          [std].[std_cont], 
          Avg([rate].[std_marks]) AS [std_marks] 
 FROM     [std_Students] [std] 
 JOIN     [std_marks] [rate] 
 ON       [std].[std_id] = [rate].[std_id] 
 WHERE    std_status='approved' 
 GROUP BY [std].[std_id], 
          [std].[std_name], 
          [std].[std_cont]

Not sure what is wrong with it.

For example, suppose these are my two tables

Table Student

Std Id

Std_name

Std_cont

101

Rameshwar

9158575801

102

Abc

0000000000

103

Xyz

1111111111

 

 

 

Table Grade

Std Id

Sub_NAme

Sub_grade

101

Math

4

101

C#

5

101

Sql

3

 

 

 


I want here to calculate the average value of all subject grades with other Details

Like

  StdID        101 
  Name         Rameshwar
  grade value: Average value of all the subject

How can I achieve it?


Asked by:- RameshwarLate
0
: 323 At:- 1/5/2018 11:11:20 AM
SQL AVG function not working avg in sql






1 Answers
profileImage Answered by:- vikas_jk

Based on your above question, I have created both the table on my local pc using the script

//1.    Student Table

CREATE TABLE [dbo].[student](

                [std_id] [int] NOT NULL,

                [std_Name] [nvarchar](50) NOT NULL,

                [std_cont] [nvarchar](50) NOT NULL,

  )

 

//2.    Grade Table

 

CREATE TABLE [dbo].[Student_grade](

[std_id] [int] NOT NULL,

[Sub_NAme] [nvarchar](50) NULL,

[Sub_grade] [int] NULL,

) 

and tried executing your query after removing "Where" clause

SELECT   [std].[std_id], 
          [std].[std_name], 
          [std].[std_cont], 
          Avg([rate].[Sub_grade]) AS [std_marks] 
 FROM     [student] [std] 
 JOIN     [student_grade] [rate] 
 ON       [std].[std_id] = [rate].[std_id] 
 GROUP BY [std].[std_id], 
          [std].[std_name], 
          [std].[std_cont]

and it works for me with AVG function as needed, here is the screenshot

SQL-server-AVG-function-min

1
At:- 1/5/2018 11:42:58 AM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use