Group by payment ID

[newbie14]

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

[urtrivedi]

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

[newbie14]

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

[urtrivedi]

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

[newbie14]

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?

[urtrivedi]

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

[newbie14]

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

[urtrivedi]

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