AVG in SQL Server

user_14787756 0
Viewed 47

I am learning SQL Server, and I have a sample database which I got from my co-worker.

I created a stored procedure previously which works fine. Now I am trying to show average of the column which I am struggling at the moment.

Your supports means a lot and I will learn from this.

So here is my query:

SELECT  
    CONCAT(ud.FirstName, ',', ud.LastName) AS 'Name',
    tech.TechnoName AS 'Techno',
    (rt.Rating) AS 'Rating', rt.FeedBackType,
    rt.Description, rt.ProgramId 
FROM 
    Rating rt, Techno tech, Program pd, User ud
WHERE 
    pd.Id = ud.UserID AND
    pd.TechnoId = tech.TechnoID AND
    rt.PdId = pd.Id
GROUP BY 
    pd.Id, ud.FirstName, ud.LastName, tech.TechnoName, 
    rt.Rating, rt.PdId, rt.Description, rt.FeedBackType
ORDER BY 
    rt.PdId

And my table is like

    Name    Techno    Rating    FeedbackType    Description     ProgramId
    ---------------------------------------------------------------------
    A,B       C#         4          1             ***              100
    A,B       C#         5          1             ***              102
    B,B       JS         4          3             ***              106
    B,C       C++        3          1             ***              280
    B,C       C          5          1             ***              300

And Now I want to show the avg rating based on the name and techno column such that my table should be like

Name    Techno     Rating    
--------------------------
A,B       C#         4.5          
B,B       JS         4          
B,C       C++        3          
B,C       C          5

Thanks in advance

aggregate-functions inner-join sql-server average sql
1 Answers

You would need to adapt the group by clause to generate the one row per name and techno, and use an aggregate function to compute the rating:

select  
    concat(ud.firstname,',',ud.lastname) as name, 
    tech.technoname as techno,
    avg(1.0 * rt.rating) as rating
from rating rt
inner join program pd on rt.pdid = pd.id 
inner join techno tech on pd.technoid = tech.technoid
inner join user ud opn pd.id = ud.userid
group by ud.firstname, ud.lastname, tech.technoname
order by ud.firstname, ud.lastname

Important notes:

  • Use standard joins! Implicit joins (with commas in the from clause and joining conditions in the where clause) are legacy syntax from decades ago, that should be used in new code

  • Do not use single quotes as quoting character for identifiers; use the relevant quoting character for your platform (in SQL Server, square brackets) - or better yet, use identifiers that do not require quoting

  • If rating is an integer, you need to turn it to a decimal before averaging it (otherwise you get an integer average)

user_106767160
Related