Subquery not working the way i want it to

Question

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. 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,
                            (select min(convert(int, income))
                            from [dbo].[marketing_campaign] where marital_status = 'Single' and year_birth > 1980 and income <> 0) Min_Single,
                            (select avg(convert(int, income))
                            from [dbo].[marketing_campaign] where marital_status = 'Single' and year_birth > 1980 and income <> 0) Avg_Single,
                            (select max(convert(int, income))
                            from [dbo].[marketing_campaign] where marital_status = 'Single' and year_birth > 1980 and income <> 0) Max_Single,
                            (select min(convert(int, income))
                            from [dbo].[marketing_campaign] where marital_status = 'together' and year_birth > 1980 and income <> 0) Min_together,
                            (select avg(convert(int, income))
                            from [dbo].[marketing_campaign] where marital_status = 'together' and year_birth > 1980 and income <> 0) Avg_together,
                            (select max(convert(int, income))
                            from [dbo].[marketing_campaign] where marital_status = 'together' and year_birth > 1980 and income <> 0) Max_together
from [dbo].[marketing_campaign]
where  education in ('master', 'Graduation', 'phd', 'basic')
group by education
order by education desc

Thanks in advance!

Answer 1

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

with i as (
    select education, 
        Iif(marital_status = 'Married' , Convert(int, income),0) Income_Married,
        Iif(marital_status = 'Single'  , Convert(int, income),0) Income_Single,
        Iif(marital_status = 'together', Convert(int, income),0) Income_Together,
    from dbo.marketing_campaign
    where education in ('master', 'Graduation', 'phd', 'basic')
    and year_birth > 1980 and income != 0
)
select education, 
    Min(Income_Married) Min_Married,
    Avg(Income_Married) Avg_Married,
    Max(Income_Married) Max_Married,
    Min(Income_Single) Min_Single,
    Avg(Income_Single) Avg_Single,
    Max(Income_Single) Max_Single,
    Min(Income_Together) Min_Together,
    Avg(Income_Together) Avg_Together,
    Max(Income_Together) Max_Together
from i
group by education
order by education desc;