Narrowing results using subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Narrowing results using subquery

Author

Topic

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 00:50:44

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

60 Posts

Posted - 2014-03-04 : 01:25:51

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)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-04 : 01:45:40

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
[spoiler]Time is always against us[/spoiler]

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 01:49:03

I think I got it right and the answer is wrong. I did that. Thanks khtan

BMW
Starting Member

1 Post

Posted - 2014-03-09 : 18:56:42

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources