GROUP BY columns of different no of rows in Mysql-CodePudding

I have a problem . I am writing a script that is following

SELECT distinct(customer_id),
(CASE WHEN (LoanCycleAgainstPartner = 1 and dpd > 0) THEN 'not paid in time' ELSE 'paid in time' END) as 'First Loan Payment',
(CASE WHEN (LoanCycleAgainstPartner = 2 and dpd > 0) THEN 'not paid in time' ELSE 'paid in time' END) as 'Second Loan  Payment',
(CASE WHEN (LoanCycleAgainstPartner = 3 and dpd > 0) THEN 'not paid in time' ELSE 'paid in time' END) as 'Third Loan Payment'
FROM database1.`2022.12.29_loan_details` 
WHERE partnername = 'Uber B.V.'
GROUP BY customer_id ;

I have attached the photo of outputOutput of my query The problems is that in 3rd and 4th column. Same value is repeating

The problem is Three columns that I want to show in output have different no of rows and I want to group By against "customer_id". 3rd and 4th column are showing only one value across the column. How I group by columns of different no of rows

CodePudding user response：

You should be aggregating the various CASE expressions:

SELECT
    customer_id,
    CASE WHEN SUM(LoanCycleAgainstPartner = 1 AND dpd > 0) > 0
         THEN 'not paid in time' ELSE 'paid in time' END AS `First Loan Payment`,
    CASE WHEN SUM(LoanCycleAgainstPartner = 2 AND dpd > 0) > 0
         THEN 'not paid in time' ELSE 'paid in time' END AS `Second Loan Payment`,
    CASE WHEN SUM(LoanCycleAgainstPartner = 3 AND dpd > 0) > 0
         THEN 'not paid in time' ELSE 'paid in time' END AS `Third Loan Payment`
FROM database1.`2022.12.29_loan_details` 
WHERE partnername = 'Uber B.V.'
GROUP BY customer_id;