How do it calculate sum of resultant column in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hello All,

Greetings for the day!

I have a two tables and based upon that i need to generate a third table with some operations. Well, am pretty much done with all the functions but i need it to calculate two resultant columns as total.

When i execute the query the resultant data stores in table3 at the same time i need to calculate SUM of two columns of table3.

Is it possible?

Update: Was googling around and found one link, tried but couldn't get any results. Hope it might be use for you all to understand my question.

Click here[^]

What I have tried:

SQL

INSERT INTO TABLE3
SELECT Capacity.[SPOC-Partner], 
COUNT(PMKVY.CentreID) as Candidate,
COUNT(case when [Aadhar Validation-Aadhar Number Validated] = 'Yes' then 1 end) as Validated,
COUNT(case when [Result Approved by SSC] = 'Pass' then 1 end) as Results_Pass,
COUNT(case when [Result Approved by SSC] = 'Fail' then 1 end) as Results_Fail,
COUNT(case when [Result Approved by SSC] = 'No Value' then 1 end) as Results_NA,
SUM(case when [BankAcountNumber] IS NULL then 0 else 1 end) as Bank,
COUNT(case when [Certified] = 'Yes' then 1 end) as Certified,
COUNT(case when [Monetary Reward Tracker-NSDC Processed] = 'Yes' then 1 end) as NSDC_Certified,
SUM(case when [Monetary Reward Tracker-Disbursement List] = '0' then 0 else 1 end)  as DL,
Capacity.[Total Target],

//Below is what am trying to do. Need to insert "Total_declared" using resultant of "Result_Pass", "Result_Fail", "Result_NA" (Ref: Above query)
-------------------------------------------------------------------------------
sum(sum( Results_Pass)+ sum(Results_Fail)+ sum(Results_NA))) as Total_declared,
-------------------------------------------------------------------------------
//

FROM PMKVY
INNER JOIN Capacity ON PMKVY.CentreID=Capacity.[Training Centre ID]
GROUP BY Capacity.[SPOC-Partner],Capacity.[Total Target]

Thanks
Saiekrishna

Posted 25-Feb-16 2:59am

Member 10376341

Updated 25-Feb-16 4:30am

CHill60

v4

Add a Solution

1 solution

Add a Solution

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

super · Answer 1 · 2016-02-25T04:30:00

Solution 1

There were two mistake in your code
1. Missing calculated Keyword
2. Using "+" operator

Try the below code and check?

SQL

INSERT INTO TABLE3
SELECT Capacity.[SPOC-Partner], 
COUNT(PMKVY.CentreID) as Candidate,
COUNT(case when [Aadhar Validation-Aadhar Number Validated] = 'Yes' then 1 end) as Validated,
COUNT(case when [Result Approved by SSC] = 'Pass' then 1 end) as Results_Pass,
COUNT(case when [Result Approved by SSC] = 'Fail' then 1 end) as Results_Fail,
COUNT(case when [Result Approved by SSC] = 'No Value' then 1 end) as Results_NA,
SUM(case when [BankAcountNumber] IS NULL then 0 else 1 end) as Bank,
COUNT(case when [Certified] = 'Yes' then 1 end) as Certified,
COUNT(case when [Monetary Reward Tracker-NSDC Processed] = 'Yes' then 1 end) as NSDC_Certified,
SUM(case when [Monetary Reward Tracker-Disbursement List] = '0' then 0 else 1 end)  as DL,
Capacity.[Total Target], 
sum(calculated Results_Pass ,calculated Results_Fail, calculated Results_NA) as Total_declared,

 
FROM PMKVY
INNER JOIN Capacity ON PMKVY.CentreID=Capacity.[Training Centre ID]
GROUP BY Capacity.[SPOC-Partner],Capacity.[Total Target]

Posted 25-Feb-16 4:30am

super

Comments

Member 10376341 26-Feb-16 2:25am

Thank you for the reply.
Well, i have tried it but i get an error:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'Results_Pass'.

super 26-Feb-16 3:03am

Try either one thing and see if it works.
1. Replace Count by SUM

or

2. put "then 1 else 0 end"

Member 10376341 26-Feb-16 3:54am

Nope! same error remains.

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'Results_Pass'.

Error is in the "line 11" that is:
sum(calculated Results_Pass ,calculated Results_Fail, calculated Results_NA) as Total_declared,

super 26-Feb-16 4:19am

how about you try sum(sum(Results_Pass),sum(Results_Fail),sum(Results_NA)))

Member 10376341 26-Feb-16 4:42am

This time the error is:

Msg 174, Level 15, State 1, Line 11
The sum function requires 1 argument(s).

super 26-Feb-16 4:50am

Are you using Stored Proc ?

Anyway now try either two options...
1. Use sum(Results_Pass)+ sum(Results_Fail)+ sum(Results_NA)
or
2. Calculated Results_Pass + Calculated Results_Pass + Calculated Results_Pass

Member 10376341 26-Feb-16 4:57am

No am not using Stored Procedure
Well when i use: sum(Results_Pass)+ sum(Results_Fail)+ sum(Results_NA)
I get this error:
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Results_Pass'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Results_Fail'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Results_NA'.

And when i use: Calculated Results_Pass + Calculated Results_Pass + Calculated Results_Pass
I get:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '+'.

super 26-Feb-16 5:04am

Try
Sum (Calculated Results_Pass + Calculated Results_Pass + Calculated Results_Pass )

Pls let me know what SQL version you are using...

Other option would be try with simple table and look.

Member 10376341 26-Feb-16 5:19am

Am using SQL Server 2014

I tried calculating with other option too but in vain, same error.

Tried:
SELECT Results_Pass, Results_Fail,Results_NA,
sum (Calculated Results_Pass + Calculated Results_Fail + Calculated Results_NA ) as Total,
FROM Partner_cre

Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'Results_Pass'.

Member 10376341 26-Feb-16 5:23am

All my work is abruptly stalled because of this error.

super 26-Feb-16 5:24am

What is the variable type of the columns.
Try to use stored procedure with local variable defined and see?

Member 10376341 26-Feb-16 5:26am

it is numeric type.

Member 10376341 26-Feb-16 5:27am

I tried with the stored procedure some time back but still am unable to get the result.

Member 10376341 26-Feb-16 5:52am

Tried:
SELECT Results_Pass, Results_Fail, Results_NA,
Total_declared = Results_Pass + Results_Fail + Results_NA
FROM Partner_cre

Got the solution. Now i need to incorporate this query in the first query, that is:

INSERT INTO TABLE3
SELECT Capacity.[SPOC-Partner],
COUNT(PMKVY.CentreID) as Candidate,
COUNT(case when [Aadhar Validation-Aadhar Number Validated] = 'Yes' then 1 end) as Validated,
COUNT(case when [Result Approved by SSC] = 'Pass' then 1 end) as Results_Pass,
COUNT(case when [Result Approved by SSC] = 'Fail' then 1 end) as Results_Fail,
COUNT(case when [Result Approved by SSC] = 'No Value' then 1 end) as Results_NA,
SUM(case when [BankAcountNumber] IS NULL then 0 else 1 end) as Bank,
COUNT(case when [Certified] = 'Yes' then 1 end) as Certified,
COUNT(case when [Monetary Reward Tracker-NSDC Processed] = 'Yes' then 1 end) as NSDC_Certified,
SUM(case when [Monetary Reward Tracker-Disbursement List] = '0' then 0 else 1 end) as DL,
Capacity.[Total Target],
sum(calculated Results_Pass ,calculated Results_Fail, calculated Results_NA) as Total_declared,

FROM PMKVY
INNER JOIN Capacity ON PMKVY.CentreID=Capacity.[Training Centre ID]
GROUP BY Capacity.[SPOC-Partner],Capacity.[Total Target]

is it possible?

super 26-Feb-16 6:18am

You have to see if it works. Then its good..

What I was try to say was
------------
Declare @Pass int;

------------
@Pass = COUNT(case when [Result Approved by SSC] = 'Pass' then 1 end) as Results_Pass,

-----
@pass + @Fail + @NA as Total_declared,

Member 10376341 26-Feb-16 6:32am

Yes i have done that previously but couldn't get the result.

super 26-Feb-16 6:35am

Not sure why is not happening.. Try the option you have your have mentioned in earlier

Member 10376341 26-Feb-16 10:48am

I'm able to execute the query individually but not in the group.
Any other possible solution?

super 29-Feb-16 8:57am

What you mean by group?

How do it calculate sum of resultant column in sql

1 solution

Solution 1

Add your solution here

Preview 0

How do it calculate sum of resultant column in sql

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us