How to add a default row at the end of result set in sql?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 31-Mar-14 23:24pm

Keerthi Kumar(Andar)

Add a Solution

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

2 solutions

Solution 2

Check this out, and adapt it to your need:
http://sqlfiddle.com/#!3/6dfc5/11[^]

Posted 1-Apr-14 0:28am

Peter Leow

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Varsha Ramnani · Accepted Answer · 2014-03-31T23:49:00

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