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
 I'm stuck please help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/03/2014 :  13:59:57  Show Profile  Reply with Quote
Hi, my first post here in this forum.
I am stuck with this for last hour. Please help.

Question:
Find all males in the customers table with an income less than or the same as the average
-- income of all customers. List last name, a comma and space, and first name in one column and yearly income.
-- 4404 rows


My answer:
SELECT C1.LastName+', '+ C1.FirstName AS FullName, C1.YearlyIncome
FROM dbo.DimCustomer AS C1
JOIN (SELECT AVG(YearlyIncome) AS AVGYR, CustomerKey FROM dbo.DimCustomer ) AS C2
ON C1.CustomerKey = C2.CustomerKey
WHERE C1.YearlyIncome <= C2.AVGYR

bitsmed
Constraint Violating Yak Guru

358 Posts

Posted - 03/03/2014 :  14:15:49  Show Profile  Reply with Quote
quote:
Originally posted by rv498

Hi, my first post here in this forum.
I am stuck with this for last hour. Please help.

Question:
Find all males in the customers table with an income less than or the same as the average
-- income of all customers. List last name, a comma and space, and first name in one column and yearly income.
-- 4404 rows


My answer:
SELECT C1.LastName+', '+ C1.FirstName AS FullName, C1.YearlyIncome
FROM dbo.DimCustomer AS C1
JOIN (SELECT AVG(YearlyIncome) AS AVGYR, CustomerKey FROM dbo.DimCustomer ) AS C2
ON C1.CustomerKey = C2.CustomerKey
WHERE
C1.YearlyIncome <= C2.AVGYR

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/03/2014 :  14:51:29  Show Profile  Reply with Quote
Where are you looking for males condition?
as per your explanation
it should be this

SELECT *
FROM
(
SELECT C1.LastName+', '+ C1.FirstName AS FullName,c1.Gender, C1.YearlyIncome,AVG(C1.YearlyIncome) OVER () AS AvgCustIncome
FROM dbo.DimCustomer AS C1
)t
WHERE Gender = 'Male'
AND YearlyIncome <= AvgCustIncome


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/03/2014 :  15:31:17  Show Profile  Reply with Quote
Thanks Visakh. Much obliged
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/03/2014 :  15:54:45  Show Profile  Reply with Quote
Thanks bitsmed, nice solution.
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.09 seconds. Powered By: Snitz Forums 2000