Sql Server 2005中两个Comman列的总和 [英] Sum of Two Comman Columns in Sql Server 2005

问题描述

嗨
我想要两个相同列的总和。

我的查询如下：

  SELECT  OMTC，OMYY，OMNO，ODSR，OMCMCD，OMLMGCST，ODSALPRC，ORRMCTG，SUM（ORSALVAL） as 总计
  FROM  ORDMST  INNER   JOIN  ORDDSG  ON  OMTC = ODTC  AND  OMYY = ODYY  AND  OMCHR = ODCHR  AND  OMNO = ODNO 
  INNER   JOIN  ORDRM  ON  ODTC = ORTC  AND  ODYY = ORYY  AND  ODCHR = ORCHR  AND  ODNO = ORNO  AND  ODSR = ORSR 
 其中 omtc = '  qt ' 和 omyy = '  13' 和 omchr = '  KPS' 和 omno = '  174' < span class =code-keyword> AND  ODSR = 2  AND  ORRMCTG  NOT   IN （'  d'，  C'）
  GROUP   BY  ODSR，ORRMCTG，OMTC，OMYY，OMNO，OMCMCD，OMLMGCST，ODSALPRC 

结果：

QT 13 174 2 KP029 1500 98.53 S 3.942

QT 13 174 2 KP029 1500 98.53 X 9.072



预期结果：

QT 13 174 2 KP02 9 1500 98.53 13.014

解决方案

从查询中删除ORRMCTG列，您将获得所需的结果。

  SELECT  OMTC 
，OMYY 
，OMNO 
，ODSR 
，OMCMCD 
，OMLMGCST 
，ODSALPRC 
，SUM（ORSALVAL） as 总计
  FROM  ORDMST 
  INNER   JOIN  ORDDSG  ON  OMTC = ODTC  AND  OMYY = ODYY  AND  OMCHR = ODCHR  AND  OMNO = ODNO 
  INNER   JOIN  ORDRM  ON  ODTC = ORTC  AND  ODYY = ORYY  AND  ODCHR = ORCHR  AND  ODNO = ORNO  AND  ODSR = ORSR 
 
  WHERE  omtc = '  qt'  AND  omyy = '  13'  AND  omchr = '  KPS'  AND  omno = '  174'  AND  ODSR = 2 
  AND  ORRMCTG  NOT   IN （'  d'，'  C'）
  GROUP   BY  ODSR，OMTC， OMYY，OMNO，OMCMCD，OMLMGCST，ODSALPRC 

Hi I want sum of two Same Columns.
My Query is as Follows

SELECT  OMTC,OMYY,OMNO,ODSR,OMCMCD,OMLMGCST,ODSALPRC,ORRMCTG,SUM(ORSALVAL) as total
FROM ORDMST INNER JOIN ORDDSG ON OMTC=ODTC AND OMYY=ODYY AND OMCHR=ODCHR AND OMNO=ODNO
INNER JOIN ORDRM ON ODTC=ORTC AND ODYY=ORYY AND ODCHR=ORCHR AND ODNO=ORNO AND ODSR=ORSR
where  omtc='qt' and omyy='13' and omchr='KPS' and omno='174' AND ODSR=2 AND ORRMCTG NOT IN ('d','C')
GROUP BY ODSR,ORRMCTG,OMTC,OMYY,OMNO,OMCMCD,OMLMGCST,ODSALPRC

Result :
QT 13 174 2 KP029 1500 98.53 S 3.942
QT 13 174 2 KP029 1500 98.53 X 9.072

Expected Result :
QT 13 174 2 KP029 1500 98.53 13.014

解决方案

Remove ORRMCTG column from your query and you''ll get desired results.

SELECT  OMTC
       ,OMYY
       ,OMNO
       ,ODSR
       ,OMCMCD
       ,OMLMGCST
       ,ODSALPRC
       ,SUM(ORSALVAL) as total
FROM ORDMST 
INNER JOIN ORDDSG ON OMTC=ODTC AND OMYY=ODYY AND OMCHR=ODCHR AND OMNO=ODNO
INNER JOIN ORDRM ON ODTC=ORTC AND ODYY=ORYY AND ODCHR=ORCHR AND ODNO=ORNO AND ODSR=ORSR

WHERE omtc='qt' AND omyy='13' AND omchr='KPS' AND omno='174' AND ODSR=2 
      AND ORRMCTG NOT IN ('d','C')
GROUP BY ODSR,OMTC,OMYY,OMNO,OMCMCD,OMLMGCST,ODSALPRC

这篇关于Sql Server 2005中两个Comman列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！