Syntax error Counting voucher value total since the beginning of the month and year in MySQL

Counting voucher value total since the beginning of the month and year in MySQL

For this, use MySQL MONTH() and YEAR() methods. Let us first create a table −

mysql> create table DemoTable1562
   -> (
   -> VoucherValue int,
   -> RechargeDate date
   -> );
Query OK, 0 rows affected (1.40 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1562 values(149,'2019-10-21');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1562 values(199,'2019-10-13');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1562 values(399,'2018-10-13');
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable1562 values(450,'2019-10-13');
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1562;

This will produce the following output −

+--------------+--------------+
| VoucherValue | RechargeDate |
+--------------+--------------+
|          149 | 2019-10-21   |
|          199 | 2019-10-13   |
|          399 | 2018-10-13   |
|          450 | 2019-10-13   |
+--------------+--------------+
4 rows in set (0.00 sec)

The current date is as follows −

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2019-10-13 |
+------------+
1 row in set (0.00 sec)

Here is the query to count voucher value total since the beginning of month and year.

mysql> select sum(VoucherValue) from DemoTable1562
   -> where month(RechargeDate)=month(curdate())
   -> and year(RechargeDate)=year(curdate());

This will produce the following output −

+-------------------+
| sum(VoucherValue) |
+-------------------+
|               798 |
+-------------------+
1 row in set (0.00 sec)
Updated on: 2019-12-12T06:49:05+05:30

136 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements