Getting the yearly income percentage for the top(100) Male and Female Seperated in 2 columns using A-CodePudding

I am trying to get the percentage of top(100) yearly income(YearlyIncome) from dbo.DimCustomer, and I want 2 columns '%M' and '%F' that shows the percentage of yearly income for Males and Females

Here's my code:

Select Top(100) 
  Case dbo.DimCustomer.Gender 
    When 'M' Then (Count(dbo.DimCustomer.Gender)*100)/sum(YearlyIncome)
  End as '%M',
  Case dbo.DimCustomer.Gender 
    When 'F' Then (Count(dbo.DimCustomer.Gender)*100)/sum(YearlyIncome)
  End as '%F'
From 
  dbo.DimCustomer
Group By 
  Gender

This is the result I am getting. Am I doing it right?

And can I show them without Null - just 2 columns side by side with 1 row which is the percentage?

CodePudding user response：

To get them into the same row you can use conditional aggregation.

SELECT
  MalePct   = SUM(CASE WHEN c.Gender = 'M' THEN c.YearlyIncome END) * 100.0 / SUM(c.YearlyIncome),
  FemalePct = SUM(CASE WHEN c.Gender = 'F' THEN c.YearlyIncome END) * 100.0 / SUM(c.YearlyIncome)
FROM (
    SELECT TOP (100)
      c.YearlyIncome,
      c.Gender
    FROM dbo.DimCustomer c
    ORDER BY c.YearlyIncome DESC
) c;