How to find that how many sells were in each city and what is proportion for each city of total in p-CodePudding

I need to find out how many sells were made in each city. After that i should convert it into percentage and find out the proportion of total for each city in percentage.

select distinct(Dimension.City.City), sum(fact.sale.Profit) as summ, 
    fact.sale.Profit * 100/(SELECT SUM(fact.sale.Profit) FROM fact.sale) as 'Percentage of Total'
    from Dimension.City 
    inner join Fact.Sale 
    on Dimension.City.[City Key] = Fact.Sale.[City Key]
    group by Dimension.city.City, fact.sale.Profit
    order by sum(fact.sale.Profit) desc

Results: (Only some part)

   City                summ       Percentage of Total        

Cherry Grove Beach    22770.00    0.0088534614705504  
Idaho City            19320.00    0.0075120279144064  
Accomac               18400.00    0.0107314684491520  
Gasport               18400.00    0.0107314684491520  
Newberg               18400.00    0.0107314684491520

CodePudding user response：

Try the following:

Select D.City, D.summ, D.summ/ SUM(D.summ) Over () As [Percentage of Total]
From
(
  Select A.City, SUM(B.Profit) As summ
  From City A
  Join Sale B
  On A.City_Key = B.City_Key
  Group By City
) D
Order By D.summ DESC

SUM(B.Profit) : to find the sum of profits for each city.

SUM(D.summ) Over () : to find the total profits among all cities, sum of sum(Profit).

See a demo from db<>fiddle.