Click here to Skip to main content
13,898,949 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 15-Feb-19 9:44am
Comments
Santosh kumar Pithani 15-Feb-19 7:42am
   
DO you have any primary key column ? your query is wrong its returns M*N records
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

   
Comments
Lisanas 15-Feb-19 23:08pm
   
your link is very usefull.. thanks
Patrice T 16-Feb-19 5:45am
   
you're welcome.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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;
   
Comments
Lisanas 15-Feb-19 23:04pm
   
SELECT
Salesman
,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' )
GROUP BY Salesman;
this will give me the result i want.. thanks
Santosh kumar Pithani 15-Feb-19 23:36pm
   
Welcome

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190306.1 | Last Updated 15 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100