现金和支票的总和从表 [英] Sum of cash and cheque from a table

问题描述

我需要从2个表中的表格中获得现金和支票金额的总和，销售员我的表格是这样的

 Salesman A Cash 21151.75 
推销员B现金34510.51 
推销员C现金21252 
推销员D现金13356 
推销员A支票13944 
推销员B支票87368.5 
推销员C支票20182 
推销员D现金23862.05 

我需要得到这样的结果

推销员现金支票
推销员A 21151.75 13944 
推销员B 34510.51 87368.5 
推销员C 21252 20182 
推销员D 13356 23862.05 

请帮助..提前感谢



我尝试过：

 SELECT Ca.Cash，Cr.Credit FROM 
（SELECT Salesman，PayMode，SUM（Collection）AS Cash 
 FROM Prospect_Detail WHERE（Purpose ='Collections'）AND（PayMode ='Cash'）
 GROUP BY Salesman，PayMode）AS Ca 
 CROSS JOIN 
（SELECT Salesman，PayMode，SUM（Collection）AS C. redit 
来自Prospect_Detail WHERE（目的='收藏'）和（PayMode ='check'）
 GROUP BY Salesman，PayMode）Cr 

解决方案

查看 pivot 子句。

在SQL查询中使用Pivot的简单方法 [ ^ ]

SQL Server：PIVOT条款 [ ^ ]

  SELECT  
推销员
，PayMode 
，SUM（ CASE   WHEN  PayMode =  现金'  THEN 集合 ELSE   0   END ） AS 现金
，SUM（ CASE   WHEN  PayMode = '  check' 那么集合 ELSE   0  < span class =code-keyword> END ） AS  check 
  FROM  Prospect_Detail 
  WHERE （目的= '  Collections'   AND  PayMode  IN （'  Cash'，' 检查'））
  GROUP   BY  Salesman，PayMode ; 

i need to get the sum of cash and cheque amount from a table in 2 columns order by salesman my table is like this

Salesman A	Cash	21151.75
Salesman B	Cash	34510.51
Salesman C	Cash	21252
Salesman D	Cash	13356
Salesman A	Cheque	13944
Salesman B	Cheque	87368.5
Salesman C	Cheque	20182
Salesman D	Cash	23862.05

and i need to get the result like this

Salesman	Cash	   Cheque
Salesman A	21151.75   13944
Salesman B	34510.51   87368.5
Salesman C	21252	   20182
Salesman D	13356	   23862.05

pls help.. thanks in advance

What I have tried:

SELECT Ca.Cash,  Cr.Credit FROM 
	(SELECT Salesman, PayMode, SUM(Collection) AS Cash
		FROM Prospect_Detail WHERE(Purpose = 'Collections') AND (PayMode = 'Cash')
		GROUP BY Salesman, PayMode) AS Ca 
		CROSS JOIN
	(SELECT Salesman, PayMode, SUM(Collection) AS Credit
        FROM Prospect_Detail WHERE(Purpose = 'Collections') AND (PayMode = 'cheque')
        GROUP BY Salesman, PayMode) Cr

解决方案

Have a look at the pivot clause.
Simple Way To Use Pivot In SQL Query[^]
SQL Server: PIVOT Clause[^]

SELECT 
   Salesman
  ,PayMode
  ,SUM(CASE WHEN PayMode = 'Cash' THEN Collection ELSE 0 END) AS Cash
  ,SUM(CASE WHEN PayMode = 'cheque' THEN Collection ELSE 0 END) AS cheque
FROM Prospect_Detail 
   WHERE (Purpose = 'Collections' AND PayMode IN('Cash','cheque'))
		GROUP BY Salesman,PayMode;

这篇关于现金和支票的总和从表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！