Fetching sum of amount for a specific year from mysql

Question

I have the following table with dummy values in mysql database:

id cnic amount depositDate receivedBy receivingZone remarks
1  11111 10000 01-Nov-2019 11111      1              Ok
2  11111 10000 07-Nov-2019 11111      1              ok

Now i want to get the sum of amount from the table for specific year (2019 in this case) where the year came from current timestamp (it may be 2020, 2021 etc depends on the current date)

Any help plz

Does this answer your question? [mysql select sum group by date](https://stackoverflow.com/questions/937652/mysql-select-sum-group-by-date) — Bluetree, Nov 18 '19 at 06:56

score 1 · Answer 1 · answered Nov 18 '19 at 06:53

1

You can try below -

select sum(amount)
from tablename
where year(depositdate)=year(now())

answered Nov 18 '19 at 06:53

Fahmi

37,315
5
22
31

Nick · Accepted Answer · 2019-11-18T07:07:53.807

1

You can use the YEAR function to get the year of the depositDate column and also the current year and then sum only the values which match:

SELECT SUM(amount) AS amount
FROM yourtable
WHERE YEAR(STR_TO_DATE(depositDate, '%d-%b-%Y')) = YEAR(CURDATE())

edited Nov 18 '19 at 07:07

answered Nov 18 '19 at 06:55

Nick

138,499
22
57
95

now how will i get the amount value means i cant get it in $amount variable – SKYz Nov 18 '19 at 07:07
@SKYz see my edit. This will make the sum available in the `amount` field of the row's data. – Nick Nov 18 '19 at 07:08
Notice: Undefined index: amount in C:\xampp\htdocs\skyz\RWF\user\pages\mcInfo.php on line 57 – SKYz Nov 18 '19 at 07:17
@SKYz that should work if you're using the current query from my answer (where I added the column alias) – Nick Nov 18 '19 at 07:18
@SKYz good to hear. you can make that change (adding `AS amount`) to any of the answers you've given. – Nick Nov 18 '19 at 07:21

Ed Bangga · Answer 3 · 2019-11-18T07:14:57.940

1

Based on your sample year, we need to recognize first the date using str_to_date

select sum(amount)
from tableA
where year(now()) =  year(str_to_date(depositdate, '%d-%b-%Y'))

edited Nov 18 '19 at 07:14

answered Nov 18 '19 at 07:03

Ed Bangga

12,879
4
16
30

@Nick, thanks, but it will still extract the year even using %M, anyways I updated my answer. https://www.db-fiddle.com/#&togetherjs=gNbQw95iUR – Ed Bangga Nov 18 '19 at 07:15
Interesting. Normally `STR_TO_DATE` is so fussy it will fall over on the slightest mismatch. – Nick Nov 18 '19 at 07:20

score 1 · Answer 4 · answered Nov 18 '19 at 07:06

1

I would write the WHERE clause to be sargable:

SELECT SUM(amount)
FROM yourTable
WHERE depositDate >= DATE_FORMAT(NOW() ,'%Y-01-01') AND
      depositDate < DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 YEAR) ,'%Y-01-01');

This approach, while a bit more verbose than the other answers which use the YEAR() function, would allow an index on the depositDate column to be used.

answered Nov 18 '19 at 07:06

Tim Biegeleisen

502,043
27
286
360

now how will i get the amount value means i cant get it in $amount variable after sql fetch it – SKYz Nov 18 '19 at 07:08

Fetching sum of amount for a specific year from mysql

4 Answers4