CODEWITHSUNDEEP
mysqlsql
Carlos Morales
Carlos Morales

Reputation: 1149

How can sum values from the previous month?

I'm trying to sum amounts from the previous month typed (SET)

Here is the structure:

CREATE TABLE tester (
date_amount date,
amount int);


INSERT INTO tester VALUES
("2014-08-01", 1000),
("2014-08-02", 1001),
("2014-08-03", 1002),
("2014-08-31", 2000),
("2014-08-31", 2000),
("2014-08-31", 2000),
("2014-09-01", 1006);

Here in the example for example

If I type year= 2014 and month= 9, I should sum automatically the last day of the month typed in this case all amounts in "2014-08-31"

Here is the demo:

SET @month := 09;
SET @year := 2014;

select sum(amount) from tester 
where   month(date_amount)= @month-1

The query must sum all amounts of the last day of previous month typed

I must have as result this:

SUM(AMOUNT)
6000

I tried this but is not the correct way because here I know the last day of the month:

SET @month := 09;
SET @year := 2014;

select sum(amount) from tester 
where   month(date_amount)= @month-1 and day(date_amount)= 31

I also tried this query but got NULL

SET @month := 09;
SET @year := 2014;

SELECT sum(amount)FROM tester 
WHERE month(date_amount)= @month-1 AND day(date_amount) =last_day(day(date_amount))

Please somebody can help me with this?

Upvotes: 0

Views: 1803

Answers (5)

Gervs
Gervs

Reputation: 1397

As stated it's better to compare a column without first appling a function

SET @month = '09', @year = '2014';

SELECT
    SUM(amount) total
FROM
    tester
WHERE
    date_amount = STR_TO_DATE(CONCAT_WS('-', @year, @month, '01'), '%Y-%m-%d') - INTERVAL 1 DAY

Upvotes: 1

Deep
Deep

Reputation: 3202

This may help :

SET @month := 09;
SET @year := 2014;

SELECT sum(amount) FROM tester 
WHERE month(date_amount)= @month-1 and date_amount = last_Day(date_amount)

Upvotes: 1

Guranjan Singh
Guranjan Singh

Reputation: 734

Instead of just providing month and year provide an actual date and then you can get the results you wanted

SET @month := 09;
SET @year := 2014;

SELECT sum(amount)FROM tester 
WHERE month(date_amount)= @month-1 AND day(date_amount) = day(last_day(date_amount))

alternatively, you could use STR_TO_DATE to convert the month and year given to date first and use that instead of @Date

Upvotes: 1

LeeG
LeeG

Reputation: 728

Something like this:

SELECT SUM(AMOUNT) FROM Tester
WHERE date_amount = LAST_DAY(STR_TO_DATE(@Month + '/00/' + @Year, '%m/%d/%Y'))

If you use a function on part of the row in the table, you force sql to do a table scan. If you convert the data to a complete field, it can use indexes in the search.

Upvotes: 1

user1261620
user1261620

Reputation: 377

Instead of using the first and last day of the month use only the year and month with TO_CHAR(YYYYMM)

select sum(amount)
 from tester 
group  by to_char(date_amount, 'YYYYMM');

In Mysql you could use DATE_FORMAT(date_amount, '%Y%m') instead of to_char

Upvotes: 0

Related Questions