Let's aggregate monthly sales with SQL

Let's aggregate monthly sales with SQL. Create a table, insert data, and aggregate monthly sales with SQL.

* The database used is the one created in here.

Create table

The table name is sale. ID, sales date and time, and sales price data.

MariaDB [kimotosystem]>create table kimotosystem.sale
    -> (
    -> id int AUTO_INCREMENT PRIMARY KEY,
    -> saled_at datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    -> book_id int NOT NULL DEFAULT 0,
    -> price int NOT NULL DEFAULT 0,
    -> quantity int NOT NULL DEFAULT 0
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [kimotosystem]>
MariaDB [kimotosystem]>desc kimotosystem.sale;
+----------+----------+------+-----+---------------------+----------------+
| Field    | Type     | Null | Key | Default             | Extra          |
+----------+----------+------+-----+---------------------+----------------+
| id       | int      | NO   | PRI | NULL                | auto_increment |
| saled_at | datetime | NO   |     | 0001-01-01 00:00:00 |                |
| book_id  | int      | NO   |     | 0                   |                |
| price    | int      | NO   |     | 0                   |                |
| quantity | int      | NO   |     | 0                   |                |
+----------+----------+------+-----+---------------------+----------------+
5 rows in set (0.01 sec)

Insert data

Insert the sales data using the SQL insert statement.

MariaDB [kimotosystem]>insert into kimotosystem.sale (saled_at, book_id, price, quantity) values 
    -> ('2020-05-03 09:20:00', 1, 800, 3),
    -> ('2020-05-08 10:00:00', 1, 800, 1),
    -> ('2020-05-17 16:00:00', 5, 2800, 1),
    -> ('2020-05-29 17:00:00', 6, 3500, 1),
    -> ('2020-06-02 09:00:00', 2, 1000, 1),
    -> ('2020-06-07 11:00:00', 2, 1000, 3),
    -> ('2020-06-16 15:00:00', 5, 2800, 1),
    -> ('2020-06-28 18:00:00', 7, 4000, 1),
    -> ('2020-07-02 10:00:00', 2, 1000, 2),
    -> ('2020-07-07 12:00:00', 2, 1000, 3),
    -> ('2020-07-16 15:00:00', 5, 2800, 1),
    -> ('2020-07-28 17:00:00', 7, 4000, 1),
    -> ('2020-08-01 09:00:00', 1, 800, 2),
    -> ('2020-08-05 11:00:00', 2, 1000, 1),
    -> ('2020-08-09 14:00:00', 3, 1500, 3),
    -> ('2020-08-24 16:00:00', 4, 2500, 2),
    -> ('2020-09-02 10:00:00', 5, 2800, 2),
    -> ('2020-09-07 11:30:00', 6, 3500, 2),
    -> ('2020-09-11 15:00:00', 3, 1500, 2),
    -> ('2020-09-26 16:30:00', 2, 1000, 1);
Query OK, 20 rows affected (0.01 sec)
Records: 20  Duplicates: 0  Warnings: 0

MariaDB [kimotosystem]>
MariaDB [kimotosystem]>select * from kimotosystem.sale;
+----+---------------------+---------+-------+----------+
| id | saled_at            | book_id | price | quantity |
+----+---------------------+---------+-------+----------+
|  1 | 2020-05-03 09:20:00 |       1 |   800 |        3 |
|  2 | 2020-05-08 10:00:00 |       1 |   800 |        1 |
|  3 | 2020-05-17 16:00:00 |       5 |  2800 |        1 |
|  4 | 2020-05-29 17:00:00 |       6 |  3500 |        1 |
|  5 | 2020-06-02 09:00:00 |       2 |  1000 |        1 |
|  6 | 2020-06-07 11:00:00 |       2 |  1000 |        3 |
|  7 | 2020-06-16 15:00:00 |       5 |  2800 |        1 |
|  8 | 2020-06-28 18:00:00 |       7 |  4000 |        1 |
|  9 | 2020-07-02 10:00:00 |       2 |  1000 |        2 |
| 10 | 2020-07-07 12:00:00 |       2 |  1000 |        3 |
| 11 | 2020-07-16 15:00:00 |       5 |  2800 |        1 |
| 12 | 2020-07-28 17:00:00 |       7 |  4000 |        1 |
| 13 | 2020-08-01 09:00:00 |       1 |   800 |        2 |
| 14 | 2020-08-05 11:00:00 |       2 |  1000 |        1 |
| 15 | 2020-08-09 14:00:00 |       3 |  1500 |        3 |
| 16 | 2020-08-24 16:00:00 |       4 |  2500 |        2 |
| 17 | 2020-09-02 10:00:00 |       5 |  2800 |        2 |
| 18 | 2020-09-07 11:30:00 |       6 |  3500 |        2 |
| 19 | 2020-09-11 15:00:00 |       3 |  1500 |        2 |
| 20 | 2020-09-26 16:30:00 |       2 |  1000 |        1 |
+----+---------------------+---------+-------+----------+
20 rows in set (0.00 sec)

Aggregate monthly sales with SQL

Use the select statement, GROUP BY clause, and aggregate function to find monthly sales.

Since sales_datetime is a datetime type column, use the date_format function to convert it to'YYYY-MM'format and perform aggregation by group by.

MariaDB [kimotosystem]>select
    ->   date_format(saled_at, '%Y-%m') as saled_at, sum(price*quantity) as price
    -> from
    ->   kimotosystem.sale
    -> group by date_format(saled_at, '%Y-%m');
+----------+-------+
| saled_at | price |
+----------+-------+
| 2020-05  |  9500 |
| 2020-06  | 10800 |
| 2020-07  | 11800 |
| 2020-08  | 12100 |
| 2020-09  | 16600 |
+----------+-------+
5 rows in set (0.00 sec)

Perl Data Analytics Tutorial