How to add 3 exec result using sql..?

Question

0.00/5 (No votes)

See more:

SQL

DECLARE @sql    NVARCHAR(MAX),
        @CompID NVARCHAR(MAX),
        @ID     NVARCHAR(MAX);
SET @CompID = 245;
SET @sql = 'SELECT SUM(Cw.Weightage) AS CompanyWeight FROM CompanyWeight CW WHERE CW.CompanyID IN (' + @CompID + ')';
SET @sql = @sql + ' SELECT SUM(LW.LocWeight) AS LocWeight FROM LocationWeight LW WHERE LW.CompanyID IN (' + @CompID + ')';
SET @sql = @sql + ' SELECT SUM(CPW.Weight) AS ContWeight FROM ContactPersonWeight CPW WHERE CPW.CompanyID IN (' + @CompID + ')';
EXEC ( @sql );

What I have tried:

I m trying to add the below Exec result ....I don't know how to add when the result came on select1 result =30 , select2 result =20 and select3 result =25..

I need output for 30+20+25 = 75

plz kindly help...SQL

Posted 11-Aug-16 2:06am

venkatesh (chennai)

Updated 11-Aug-16 2:25am

v2

Add a Solution

1 solution

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)

divya behera · Accepted Answer · 2016-08-11T02:24:00

Solution 1

SQL

SELECT (SELECT SUM(Cw.Weightage) AS CompanyWeight FROM CompanyWeight CW WHERE CW.CompanyID IN (@CompID )) + (SELECT SUM(LW.LocWeight) AS LocWeight FROM LocationWeight LW WHERE LW.CompanyID IN (@CompID ))+ (SELECT SUM(CPW.Weight) AS ContWeight FROM ContactPersonWeight CPW WHERE CPW.CompanyID IN ( @CompID))

try this!

[Edit-karthik]: added in code block.

Posted 11-Aug-16 2:24am

divya behera

Updated 12-Aug-16 0:44am

v3

Comments

venkatesh (chennai) 11-Aug-16 8:42am

Thank u...its working...and i have another doubt..?

set @LocCount = 'SELECT COUNT(Cw.LocWeight) FROM LocationWeight Cw where Cw.CompanyID = 245'

how to divide the above count value to
SELECT SUM(Cw.Weightage / @LocCount) AS CompanyWeight
FROM CompanyWeight CW
WHERE CW.CompanyID IN( @CompID ))

divya behera 11-Aug-16 10:07am

declare @LocCount int;
set @LocCount = SELECT COUNT(Cw.LocWeight) FROM LocationWeight Cw where Cw.CompanyID = 245;

SELECT SUM(Cw.Weightage / @LocCount) AS CompanyWeight
FROM CompanyWeight CW
WHERE CW.CompanyID IN( @CompID ))

Can you try the above one!