-- create
CREATE TABLE Transactions (
id           int ,     
country        varchar (3), 
state          varchar (10),
amount         int,     
trans_date     date
);

-- insert
INSERT INTO Transactions(id,country,state,amount,trans_date) VALUES (121, 'US', 'approved',1000,'2018-12-18');
INSERT INTO Transactions(id,country,state,amount,trans_date) VALUES (122, 'US', 'declined',2000,'2018-12-19');
INSERT INTO Transactions(id,country,state,amount,trans_date) VALUES (123, 'US', 'approved',2000,'2019-01-01');
INSERT INTO Transactions(id,country,state,amount,trans_date) VALUES (124, 'DE', 'approved',2000,'2019-01-07');

-- fetch 
SELECT * FROM Transactions;
/*+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
Output: 
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+----------------
*/

SELECT CONCAT(YEAR(trans_date),'-',MONTH(trans_date)) AS MONTH,
       COUNTRY,
       COUNT(STATE),
       SUM(CASE WHEN STATE='approved' THEN 1 ELSE 0 END) AS approved_count,
       SUM(amount) AS trans_total_amount,
       SUM(CASE WHEN STATE='approved' THEN amount ELSE 0 END) AS approved_total_amount


FROM Transactions
GROUP BY CONCAT(YEAR(trans_date),'-',MONTH(trans_date)),COUNTRY
ORDER BY MONTH,COUNTRY DESC
by