MSSQL divide 2 sub queries / aggregate_functions

Hi I'm trying to divide & average out ratingsum / filenamecount.

When I add  
(SELECT COALESCE(ratingsum,1) / COALESCE(filenamecount,1)) as divnumber
I receive an sql server error stating that either columns doesn't exist

SELECT filename, rating,
                          (SELECT     SUM(rating) AS Expr1
                            FROM         mytable AS GV
                            WHERE      GV.filename = a.filename) AS ratingsum,
                          (SELECT     COUNT(*) AS Expr2
                            FROM          mytable AS GV
                            WHERE       GV.filename = a.filename) AS filenamecount,
                   (SELECT COALESCE(ratingsum,1) / COALESCE(filenamecount,1)) as divnumber
FROM         mytable AS a

Open in new window

m2ewAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
try this:

select t.*, (SELECT COALESCE(ratingsum,1) / COALESCE(filenamecount,1)) as divnumber
from (
SELECT filename, rating,
                          (SELECT     SUM(rating) AS Expr1
                            FROM         mytable AS GV
                            WHERE      GV.filename = a.filename) AS ratingsum,
                          (SELECT     COUNT(*) AS Expr2
                            FROM          mytable AS GV
                            WHERE       GV.filename = a.filename) AS filenamecount
FROM         mytable AS a
) x
0
 
Rajkumar GsSoftware EngineerCommented:
There is no FROM <tablename> part for that sub-query. So field are not identified
0
 
Ephraim WangoyaCommented:
select *, (SELECT COALESCE(ratingsum,1) / COALESCE(filenamecount,1)) as divnumber
(
SELECT filename, rating,
                          (SELECT     SUM(rating) AS Expr1
                            FROM         mytable AS GV
                            WHERE      GV.filename = a.filename) AS ratingsum,
                          (SELECT     COUNT(*) AS Expr2
                            FROM          mytable AS GV
                            WHERE       GV.filename = a.filename) AS filenamecount
                   
FROM         mytable AS a )
0
 
m2ewAuthor Commented:
Switched Select t.* to Select x.* for correct alias
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.