Dear All,
I got a table as below where it can capture for a particular deposit different type of payment such as cash,cheque,credit card etc where I give each type an id and represented via the paymentID column.
Below is part of my query and where I am stuck is that I want to show the total cash,credit card ,cheques etc for a particular deposit. I know I need to play around with group but when I group by then I get separate query results for different payment type.

Select Cast(Concat('DP',Concat(tblOutlet.outletCode,tblDepositpaymentdetails.depositID)) As Char) as receiptID, 
tblOutlet.outletCode,                            tblEmployee.employeeUserName,                            Sum(tblDepositpaymentdetails.amount) As total,                            Cast(DATE_FORMAT(tblDepositpaymentdetails.depositPaymentDetailsDate, '%W') As Char) As Day,                              Cast(DATE_FORMAT(tblDepositpaymentdetails.depositPaymentDetailsDate, '%d/%m/%y') As Char) As Date
CREATE TABLE `tbldepositpaymentdetails` (                            
`depositPaymentDetailsID` INT(10) NOT NULL DEFAULT '0',            
`depositID` INT(10) NOT NULL DEFAULT '0',                          
`outletID` INT(2) NOT NULL,                                        
`employeeID` INT(2) NOT NULL DEFAULT '0',                          
`paymentTypeID` INT(2) NOT NULL DEFAULT '0',                       
`bankID` INT(2) NOT NULL DEFAULT '0',                              
`creditCardTypeID` INT(2) NOT NULL DEFAULT '0',                    
`chequeCreditCardNo` VARCHAR(16) NOT NULL DEFAULT '0',             
`authorizationCode` VARCHAR(20) NOT NULL DEFAULT '0',              
`monthsID` INT(2) NOT NULL DEFAULT '0',                            
`amount` DOUBLE(10,2) NOT NULL DEFAULT '0.00',                     
`serviceChargeAmount` DOUBLE(5,2) NOT NULL DEFAULT '0.00',         
`depositPaymentDetailsDate` DATE NOT NULL,                         
`depositPaymentDetailsTime` TIME NOT NULL,                         
`depositPaymentDetailsStatus` ENUM('y','r') NOT NULL DEFAULT 'y',  
PRIMARY KEY (`depositPaymentDetailsID`,`depositID`,`outletID`)     
) ENGINE=INNODB DEFAULT CHARSET=latin1

Recommended Answers

what is ur sample data in tbldepositpaymentdetails, then also what u want as output
post sample input and sample output here

Jump to Post
select depositid, paymenttypeid, sum(amount) total from tbldepositpaymentdetails
group by depositid, paymenttypeid
order by depositid, paymenttypeid
Jump to Post

All 7 Replies

what is ur sample data in tbldepositpaymentdetails, then also what u want as output
post sample input and sample output here

Dear Utrivedi,
Say below is samples input data I made it simplified remove all the other fields.Before I proceed paymentID=1(Cash) and paymentID=2(Cheque) So in this case when I generate the report for depositPaymentDetailsDate between '2011-12-12' and '2011-12-13'. I will get one as depositID=1 cash=200 and cheque=300 and another data as depositID=2 cash=100 and cheque=450. Hope I am clearer now.

depositPaymentDetailsID depositID outletID paymentTypeID amount depositPaymentDetailsDate

1                       1         1         1            200  2011-12-12
2                       1         1         2            300  2011-12-12
3                       2         1         1            100  2011-12-13
4                       2         1         2            150  2011-12-13
5                       2         1         2            300  2011-12-13
select depositid, paymenttypeid, sum(amount) total from tbldepositpaymentdetails
group by depositid, paymenttypeid
order by depositid, paymenttypeid

Dear Utrivedi,
The problem is that I want in a single query for the particular depositID to show me how much of cash,cheque,credit card etc. Here when I run like this group by paymentTypeID I get separate results for each?

for deposit id=1 all cash,cheque,credit with breakup

select depositid, paymenttypeid, sum(amount) total from tbldepositpaymentdetails
where depositid=1 
group by depositid, paymenttypeid
order by depositid, paymenttypeid

for deposit id=1 all total amount

select depositid, paymenttypeid, sum(amount) total from tbldepositpaymentdetails
where depositid=1 
group by depositid
order by depositid

Dear Urtrivedi,
I am trying to look into something like this. Is this possible but how to achieve this, must I do 3 left joins?

SELECT depositid, cash,creditcard,cheque sum(amount) total FROM tbldepositpaymentdetails
WHERE depositid=1
GROUP BY depositid, paymenttypeid
ORDER BY depositid, paymenttypeid

if 1 is cash, 2 is credit, 3 is cheque and so on
use following query

SELECT depositid, sum(if (paymenttypeid=1,amount,0)) cash,
sum(if (paymenttypeid=2,amount,0)) credit,
sum(if (paymenttypeid=3,amount,0)) cheque,
sum(if (paymenttypeid=4,amount,0)) anyother,
sum(amount) total 
FROM tbldepositpaymentdetails
GROUP BY depositid
ORDER BY depositid
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.