SQL Qs - Monthly Transaction I

LeetCode Q 1193 - Monthly Transaction I

Given a table Transactions, write a SQL solution to get the amount of transactions, total amount of all transactions, approved transactions, the amount of all approved transactions of each country in each month.

For example,

The result is

Solution:

SELECT 
DATE_FORMAT(trans_date, "%Y-%m") AS month,
country,
COUNT(*) AS trans_count,
SUM(state = "approved") AS approved_count,
SUM(amount) AS trans_total_amount,
SUM(IF(state = "approved", amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY DATE_FORMAT(trans_date, "%Y-%m"), country

Tips:

1. MySQL DATE_FORMAT() Function

Syntax: DATE_FORMAT(date, foramt).
Fromat: %Y : Year as a numeric, 4-digit value, %m : Month name as a numeric value(00 to 12), %M: Month name is full (January to December)… More detailed explanation can be found here

2. MySQL IF() Function

Syntax: IF(condition, value_if_true, value_if_false).
Example: SELECT IF(500<1000, 5, 10); => Return 5 if the condition is TRUE, or 10 if the condition is FALSE.

3. SUM() vs. COUNT()

SUM() is doing mathematical sum, whereas COUNT() simply counts any value as 1 regardless of what data type.
Example:
SELECT SUM(state = 'approved') vs. SELECT COUNT(state = 'approved).
The first query returns the number of times the condition is true, because true is 1 and false is 0.
The second query returns the complete record count because COUNT() does not care about the content inside it, as long as the content is NOT NULL. If you would like to make the second query corrct, then we can do it like this,
SELECT COUNT(CASE WHEN state = 'approved' THEN 'no matter what' else NULL END) FROM table