现金和支票的总和从表 [英] 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屋!
查看全文