它如何计算sql中结果列的总和 [英] How do it calculate sum of resultant column in sql

问题描述

大家好，



当天的问候！



我有两张桌子和基础在那我需要生成一个带有一些操作的第三个表。好吧，我已经完成了所有的功能，但我需要它来计算两个结果列的总和。



当我执行查询时，结果数据存储在table3中，同时我需要计算table3的两列的SUM。



有可能吗？



更新：谷歌搜索并找到一个链接，试过但不能得到任何结果。希望大家都能理解我的问题。



单击此处 [ ^ ]



我是什么尝试过：

  INSERT   INTO  TABLE3 
  SELECT 容量。[SPOC-Partner]，
 COUNT（PMKVY.CentreID）< span class =code-keyword> as 候选人，
 COUNT（案例 何时 [Aadhar Validation-Aadhar Number Validated] = ' 是' 然后  1   end ） as 已验证，
 COUNT（案例 何时 [结果已批准 by  SSC] = '  Pass' 然后  1   end ）< span class =code-keyword> as  Results_Pass，
 COUNT（ case  何时 [结果已批准按 SSC] = ' 失败     1   end ）  as  Results_Fail，
 COUNT（ case   when  [结果已批准按 SSC] = ' < span class =code-string> No Value'    1   end ） as  Results_NA，
 SUM（ case  当 [BankAcountNumber]  IS   NULL  然后  0   else   1  结束） as 银行，
 COUNT（ case  何时 [Certified] = ' 是' 然后  1  结束） as 认证，
 COUNT（ case  当 [Monetary Reward Tracker-NSDC Processed] = ' 是'     1   end ）  as  NSDC_Certified，
 SUM（ case  何时 [Monetary Reward Tracker-Disbursement List] = '  0' 然后  0  其他  1   end ） as  DL，
容量。[Total 目标]，
 
 //  正在尝试 做。需要到 插入  结果   Result_Pass，  Result_Fail，  Result_NA（参考：以上查询）
   -    -------------------------- -------------------------------------------------- -   
 sum（sum（Results_Pass）+ sum（Results_Fail）+ sum（Results_NA）））  Total_declared，
   -    -------------------------- -------------------------------------------------- -   
 // 
 
  FROM  PMKVY 
  INNER   JOIN 容量 ON  PMKVY.CentreID =容量。[培训中心ID] 
  GROUP   BY 容量。[SPOC-合作伙伴]，容量。[总目标] 

谢谢

Saiekrishna

解决方案

你的代码中有两个错误

1.遗失计算的关键字

2.使用+运算符



尝试以下代码并检查？

  INSERT   INTO  TABLE3 
  SELECT 容量。[SPOC-Partner]，
 COUNT（PMKVY.CentreID） as 候选人，
 COUNT（案例 何时 [Aadhar Validation-Aadhar Number Validated] = ' 是' 然后  1   end ） as 已验证，
 COUNT（案例 何时 [结果已批准 by  SSC] = ' 传递' 然后  1   end ） as  Results_Pass，
 COUNT（案例 何时 [结果已批准 by  SSC] = ' 失败' 然后  1   end ）< span class =code-keyword> as  Results_Fail，
 COUNT（ case  何时 [结果已批准按 SSC] = ' 无值' 然后  1   end  ） as  Results_NA，
 SUM（ case   when  [BankAcountNumber]  IS   NULL  然后  0   else   1  结束） as  Bank，
 COUNT（ case    [Certified] = ' 是' 然后  1  结束） as 认证，
 COUNT（ case  何时 [Monetary Reward Tracker-NSDC Processed] = ' 是'    1   end ） as  NSDC_Certified，
 SUM（ case  何时 [货币奖励追踪器 - 支付清单] = '  0' 然后  0   else   1  < span class =code-keyword> end ） as  DL，
 Capacity。[Tot al 目标]，
 sum（计算Results_Pass，计算Results_Fail，计算Results_NA）  Total_declared， 
 
 
  FROM  PMKVY 
  INNER   JOIN 容量 ON  PMKVY.CentreID =容量。[培训中心ID] 
  GROUP   BY 容量。[SPOC-Partner]，容量。[总目标 ] 

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:

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

解决方案

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

Try the below code and check?

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]

这篇关于它如何计算sql中结果列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！