Subquery not working the way i want it to-CodePudding

This is my first ever post here on stackoverflow.

I would like to see what the min, average and max salary is per education level per marital_status (in this case married). I've tried this code but it only shows the same value for min, average and max for all the different education levels:

Select distinct education, (select min(convert(int, income))
                                from [dbo].[marketing_campaign] where marital_status = 'Married' and year_birth > 1980 and income <> 0) Min_Married,
                                (select avg(convert(int, income))
                                from [dbo].[marketing_campaign] where marital_status = 'Married' and year_birth > 1980 and income <> 0) Avg_Married,
                                (select max(convert(int, income))
                                from [dbo].[marketing_campaign] where marital_status = 'Married' and year_birth > 1980 and income <> 0) Max_Married 
    from [dbo].[marketing_campaign]
    where year_birth > 1980
    and education in ('master', 'Graduation', 'phd', 'basic')
    group by education, marital_status
    order by education desc

Thanks in advance!

CodePudding user response：

There is no correlation with education in your sub-queries, neither is there any need for repeatedly using a sub-query when I suspect all you need is the following:

Select education, 
    Min(convert(int, income)) Min_Married,
    Avg(convert(int, income)) Avg_Married,
    Max(convert(int, income)) Max_Married 
from dbo.marketing_campaign
where year_birth > 1980
    and education in ('master', 'Graduation', 'phd', 'basic')
    and marital_status = 'Married'
group by education
order by education desc;

CodePudding user response：

You can simply do this as follow. I tried to make the same environment as you describe in my DB, so hope this will help you.

SELECT   DISTINCT C.education,
         MIN(income) OVER(PARTITION BY education) AS MIN_Married, 
         AVG(income) OVER(PARTITION BY education) AS AVG_Married, 
         MAX(income) OVER(PARTITION BY education) AS MAX_Married 
FROM     [dbo].[marketing_campaign] C
WHERE    education IN ('master', 'Graduation', 'phd', 'basic')
         AND marital_status = 'Married'
         AND year_birth > 1980
ORDER BY education DESC