0

I have this data

enter image description here

I want to have name,jan,feb,mar columns. i used Case to pivot like this

 select name,
    CASE WHEN datepaid LIKE '%-01-%' THEN amountpaid ELSE 0 END AS jan, 
    CASE WHEN datepaid LIKE '%-02-%' THEN amountpaid ELSE 0 END AS feb, 
    CASE WHEN datepaid LIKE '%-03-%' THEN amountpaid ELSE 0 END AS mar 
FROM payments;

enter image description here

I DO NOT WANT THE NAME to repeat. When I group by name I end up with the last records of the respective names as shown

select name,
    CASE WHEN datepaid LIKE '%-01-%' THEN amountpaid ELSE 0 END AS jan, 
    CASE WHEN datepaid LIKE '%-02-%' THEN amountpaid ELSE 0 END AS feb, 
    CASE WHEN datepaid LIKE '%-03-%' THEN amountpaid ELSE 0 END AS mar 
FROM payments 
group by name;

enter image description here

Kindly help Here is SQL DATA.

CREATE TABLE `payments` (
  `datepaid` date DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `amountpaid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `payments` (`datepaid`, `name`, `amountpaid`) VALUES
('2020-01-13', 'james', 150),
('2020-01-14', 'mercy', 150),
('2020-01-16', 'otieno', 150),
('2020-01-17', 'njoroge', 100),
('2020-02-18', 'james', 150),
('2020-02-20', 'mercy', 150),
('2020-02-21', 'otieno', 150),
('2020-03-15', 'william', 50),
('2020-03-19', 'njoroge', 100),
('2020-03-22', 'william', 50),
('2020-03-23', 'njoroge', 100);
kimoduor
  • 504
  • 6
  • 16
  • Please post code, errors, sample data or textual output here as plain-text, not as images that can be hard to read, can’t be copy-pasted to help test code or use in answers, and are barrier to those who depend on screen readers. You can edit your question to add the code in the body of your question. For easy formatting use the `{}` button to mark blocks of code, or indent with four spaces for the same effect. The contents of a **screenshot can’t be searched, run as code, or copied and edited to create a solution.** – tadman Nov 24 '20 at 07:24
  • 1
    Use `SUM(CASE WHEN ... END)` – Barmar Nov 24 '20 at 07:30
  • Someone marked this question as a duplicate yet it was not. Thank you @Barmar. It worked – kimoduor Nov 24 '20 at 08:32
  • In which case it obviously is – Strawberry Nov 24 '20 at 08:44
  • That was me, since the duplicate should have the same solution I put in my comment. – Barmar Nov 24 '20 at 15:18

0 Answers0