Click here to Skip to main content
15,946,179 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi experts,

I have got this output by below mensioned query
IPS_Facility_Name                      percentage
abcefg Certficate                            28
PQRSTUV Certificate                      21
XYZABC Certificate                        16
xlmnopq Certificate                       9
Yojane cirtificate                            6
Others                                         20

Query
SQL
Select top 5 IPS_Facility_Name,
        convert(decimal,(convert(decimal,SUM(IPS_TotRaised))/convert(decimal,@totReceived))*100) as percentage
        from Intermediate_MonthWise_Statistics
        --where IPS_Month =@month  and IPS_Year =@year
        group by IPS_Facility_Name
        having SUM(IPS_TotRaised)>0
        order by SUM(IPS_TotRaised) desc


I want to sum the percentage column and that will be subtracted from 100 this result i want to add as a new row that is 100-(Sum(PercentageColumn))=20

Plese help me to calculate others row in sql query

(thanks in advance)
(Keerthi Kumar)
Posted
Comments
Varsha Ramnani 1-Apr-14 5:44am    
you can use temptable to store the result of this query and then use sum(PercentageColumn) and Union

You can use TempTable as follows :

DECLARE @TempTable TABLE
	(	
		IPS_Facility_Name VARCHAR(100),
		FacilityPercentage int		
	)
	
	INSERT INTO @TempTable
	        ( IPS_Facility_Name,FacilityPercentage )
	Select top 5 IPS_Facility_Name,
        convert(decimal,(convert(decimal,SUM(IPS_TotRaised))/convert(decimal,@totReceived))*100) as percentage
        from Intermediate_MonthWise_Statistics
        --where IPS_Month =@month  and IPS_Year =@year
        group by IPS_Facility_Name
        having SUM(IPS_TotRaised)>0
        order by SUM(IPS_TotRaised) DESC
        
        INSERT INTO @TempTable
                ( IPS_Facility_Name,FacilityPercentage )
        SELECT 'Total Percentage',100 - SUM(FacilityPercentage) FROM @TempTable
        
        SELECT * FROM @TempTable
 
Share this answer
 
Comments
Keerthi Kumar(Andar) 2-Apr-14 2:11am    
thanks a lot Varsha Ramnani its working
Check this out, and adapt it to your need:
http://sqlfiddle.com/#!3/6dfc5/11[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900