By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table transactions (
id int,
user_id int,
amount int,
transaction_mode varchar(50),
created_date date
);
insert into transactions values
(1, 1, 55, 'Joined the Contest', '2023-10-07'),
(2, 1, 25, 'For Winning the Contest', '2023-10-07'),
(3, 2, 33, 'Joined the Contest', '2023-10-12'),
(4, 2, 10, 'For Winning the Contest', '2023-10-12'),
(5, 2, 58, 'Joined the Contest', '2023-10-26'),
(6, 2, 22, 'Joined the Contest', '2023-10-26'),
(7, 1, 40, 'For Winning the Contest', '2023-10-26'),
(8, 1, 10, 'For Winning the Contest', '2023-10-26');
8 rows affected
SELECT SUM(CASE WHEN transaction_mode = 'Joined the Contest' THEN amount END) as entry_amount,
SUM(CASE WHEN transaction_mode = 'For Winning the Contest' THEN amount END) as credited_amount,
created_date as [date]
FROM transactions
GROUP BY created_date
| entry_amount | credited_amount | date |
|---|---|---|
| 55 | 25 | 2023-10-07 |
| 33 | 10 | 2023-10-12 |
| 80 | 50 | 2023-10-26 |
Warning: Null value is eliminated by an aggregate or other SET operation.