SQL Query

Hi;

I am creating a report that shows me general information about customers. I am stuck in the CASE WHEN statement for a payment type because it is grouping by payment type and I don't want this, I need every payment type result in a different column, this is the query that I have so far:

select
PL.ID,
LastName AS 'last Name',
PRP.PostUseFee AS 'Additional Charges',
SUM(I.ChargedAmount) AS 'Total Charges',
CASE WHEN I.PaymentMethod = 'CC' THEN SUM(I.ChargedAmount) ELSE NULL END AS 'Charges by CC'
from Person Pe
Join ProductLine PL on Pe.ID = PL.PersonID
left Join ProductRatePlan PRP on PL.ProductRatePlanID = PRP.ID
left Join Invoice I on PL.ID = I.ProductLineId
Group by PL.ID, ProductRatePlanID, LastName, PRP.PostUseFee,
I.PaymentMethod


ID Last Name AdditionalCharges TotalCharges CC
3360 Roberts $20.00 $30.00 $30.00
3361 Mars $10.00 $40.00 $40.00
3361 Mars $10.00 $0.00 Null
3362 Jimenez $25.00 $45.00 $45.00

the results came with duplicates ProductLine IDs if we have more than one payment method for each Product line, and what I really need is no duplicates Product lines and the sum of the charges in different columns for each payment type, something like this:

ID Last Name AdditionalCharges TotalCharges CC Check
3360 Roberts $20.00 $30.00 $30.00 $0.00
3361 Mars $10.00 $40.00 $15.00 $25.00
3362 Jimenez $25.00 $45.00 $45.00 $0.00

Is like I dont want to GroupBy Payment type, but I have to include Payment type in the group by clause because I dont have aggregate function.

Please help me.

 

I think we need some more information in order to help with the issue. Can you post the raw data? I'm having a hard time understanding where the numbers are coming from...

 

Can you also post what SQL engine you are using?