Solved

MSSQL divide 2 sub queries / aggregate_functions

Posted on 2011-02-22
4
1,289 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:m2ew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 250 total points
ID: 34954165
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34954171
There is no FROM <tablename> part for that sub-query. So field are not identified
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34954215
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
 

Author Closing Comment

by:m2ew
ID: 34954401
Switched Select t.* to Select x.* for correct alias
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
SQL Server Database Inventory 7 33
Creating a View from a CTE 15 50
Do not display comma when no last name 8 48
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question