Select organizations that their income represent around 60% of the total income SQL Server2008

Question

In this data warehouse, we have organization which composed of multiple Organizations,

I have [FactFinance] table which has information about the income of each organization.

I have the following query in data warehouse which select the (Organization Name) from the [organization dimension table] and its (Total Income Amount) from [FactFinance table] and what its (Total Income Amount) represent as a percentage from the total income amount of the whole organization

select  [DimOrganization].[OrganizationName] ,  SUM([Amount])as TotalAmountIncome , SUM([FactFinance].[Amount])/(select sum([FactFinance].[Amount]) 
from   [FactFinance] ) PercentageOfTotalSales
from   [FactFinance] , [DimOrganization]
Where [FactFinance].[OrganizationKey]= [DimOrganization].[OrganizationKey]
group by ([DimOrganization].[OrganizationName])
order by SUM([Amount]) desc

The Result is the following table:

+--------------------+-------------------+------------------------+
|  OrganizationName  | TotalAmountIncome | PercentageOfTotalSales |
+--------------------+-------------------+------------------------+
| Canadian Division  | 292174782.72      | 0.215049383184007      |
| Southeast Division | 279284663.6       | 0.205561869784944      |
| Southwest Division | 232365970.6       | 0.171028307731715      |
| Central Division   | 149032081.6       | 0.109692071726198      |
| Northwest Division | 138922888.8       | 0.102251403315702      |
| Northeast Division | 134003346.4       | 0.0986304728958399     |
| France             | 66963799.96       | 0.0492873606099529     |
| Australia          | 35553074.99       | 0.0261681270906303     |
| Germany            | 30339804.03       | 0.0223310036610101     |
+--------------------+-------------------+------------------------+

I want to modify this query in order to select only the organizations that have the maximum income and represent almost 60% of the total income of the whole organization.

if I calculate the percentage in the first three rows, which are the organizations that have the maximum income

+------------------------+
| PercentageOfTotalSales |
+------------------------+
| 0.215049383            |
| 0.20556187             |
| 0.171028308            |
+------------------------+

the result will be: 0.591639561 it is almost 60%

so the result must be the first three rows (which means almost 60% of the total income of the whole organization?)

I tried the following query, but it gives me error (Invalid column name 'PercentageOfTotalSales').

select  [DimOrganization].[OrganizationName] ,  SUM([Amount])as TotalAmountIncome , SUM([FactFinance].[Amount])/(select sum([FactFinance].[Amount]) 
from   [FactFinance] ) PercentageOfTotalSales
from   [FactFinance] , [DimOrganization]
Where [FactFinance].[OrganizationKey]= [DimOrganization].[OrganizationKey]
 AND SUM(PercentageOfTotalSales) >= 0.60
group by ([DimOrganization].[OrganizationName]) 

order by SUM([Amount]) desc

I tried different ways, but it always gives me errors.

can I get any advice?

yes, close to 60% but they should be the organizations that has the most income, even if I put the range between 58% and 60% , so it can give me range close to 60% — amal50, May 21 '15 at 16:12

score 2 · Accepted Answer · answered May 21 '15 at 21:42

you can use Running Total query and use your returned table as sub query as the following:

    SELECT  [DimOrganization].OrganizationName , Value  from (


SELECT   x, Value, (SELECT SUM(Value) FROM (select top 10  [FactFinance].[OrganizationKey] as x,SUM([Amount]) as [Amount] , SUM([Amount]) / 
(select sum([Amount]) from [FactFinance])as Value

FROM [FactFinance] group by([FactFinance].[OrganizationKey]) order by SUM([Amount]) desc) T2  

WHERE T1.value <= T2.value) AS RunningTotal

FROM (select top 10  [FactFinance].[OrganizationKey] as x,SUM([FactFinance].[Amount])as [Amount] , SUM([FactFinance].[Amount])/(select sum([FactFinance].[Amount]) 
from [FactFinance])as Value
from [FactFinance] group by([FactFinance].[OrganizationKey]) order by SUM([FactFinance].[Amount]) desc) T1 ) allTable , [DimOrganization] where allTable.x = [DimOrganization].OrganizationKey AND RunningTotal<0.6

TTeeple · Answer 2 · 2015-05-21T19:04:13.373

Without having a table structure to test against, this should work.

SELECT [DimOrganization].[OrganizationName]
    ,SUM([Amount])as TotalAmountIncome
    ,SUM([FactFinance].[Amount])/TotalSales.Amount) PercentageOfTotalSales
FROM [FactFinance]
INNER JOIN [DimOrganization]
    ON [FactFinance].[OrganizationKey]= [DimOrganization].[OrganizationKey]
OUTER APPLY (
    SELECT SUM([FactFinance].[Amount])  Amount
    FROM [FactFinance]
    ) TotalSales
GROUP BY [DimOrganization].[OrganizationName]
HAVING SUM(PercentageOfTotalSales) >= 0.60
ORDER BY SUM([Amount]) DESC

As for the varying percent, you can implement that using a view and passing in a range of tolerable values.

Corrected. Forgot to close off SUM and add column name. – TTeeple May 21 '15 at 19:04 — TTeeple, May 21 '15 at 19:04

Select organizations that their income represent around 60% of the total income SQL Server2008

2 Answers2