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

---

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