SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Narrowing results using subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  00:50:44  Show Profile  Reply with Quote
Use a subquery to narrow the results to only those customers with a yearly income that is greater than
-- or the same as the average of all customers.
-- 883 rows

SELECT C.LastName+', '+C.FirstName AS FullName, C.YearlyIncome
FROM dbo.DimCustomer AS C
JOIN (SELECT AVG(YearlyIncome) AS AVGIncome FROM dbo.DimCustomer) AS D
ON C.YearlyIncome >= D.AVGIncome

after executing this, I got a whopping 9000+ rows. How can I narrow down to 883 rows?

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  01:25:51  Show Profile  Reply with Quote
So I changed to this but still wrong number of rows are returned

SELECT C.LastName+', '+C.FirstName AS FullName, C.YearlyIncome
FROM dbo.DimCustomer AS C
WHERE C.YearlyIncome >= (SELECT AVG(YearlyIncome) FROM dbo.DimCustomer)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 03/04/2014 :  01:45:40  Show Profile  Reply with Quote
to verify your result

step 1 : execute this and see what is the avg income
SELECT AVG(YearlyIncome) FROM dbo.DimCustomer


step 2 : execute this which are these customers
SELECT C.LastName+', '+C.FirstName AS FullName, C.YearlyIncome
FROM   dbo.DimCustomer AS C
WHERE  C.YearlyIncome >= <the avg income from step 1>



KH
Time is always against us

Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  01:49:03  Show Profile  Reply with Quote
I think I got it right and the answer is wrong. I did that. Thanks khtan
Go to Top of Page

BMW
Starting Member

USA
1 Posts

Posted - 03/09/2014 :  18:56:42  Show Profile  Reply with Quote
I have here the correct answer to the problem if anyone if interested:

SELECT DISTINCT C.LastName+', '+C.FirstName AS FullName, C.YearlyIncome
FROM dbo.DimCustomer AS C
WHERE C.YearlyIncome >=
(SELECT AVG(YearlyIncome) AS AvgYearlyIncome
FROM dbo.DimCustomer AS C2
WHERE EXISTS (SELECT SurveyResponseKey
FROM dbo.FactSurveyResponse
WHERE C.CustomerKey = CustomerKey))

This gives you 883 rows
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000