CODEWITHSUNDEEP
mysqldatetimesum
Cyromancer
Cyromancer

Reputation: 15

Add Result From Previous Year

I have query result for show sum result from "2019", here's my query and result :

SELECT 
    SUM(Number1 - Number2) AS TOTAL 
FROM 
    `myTable` 
WHERE 
    YEAR(Date) = '2019'
total
136

And then i wanna add sum result from "2019" with sum result from January 2020, i already tried this query but the result not as i expected :

SELECT 
    SUM(Number1 - Number2) AS TOTAL 
FROM 
    `myTable` 
WHERE 
    YEAR(Date) <= '2020' AND MONTH(DATE) <='1'

Result from query |total| |:----| | 36 |

Expected result |total| |:----| | 139 |

im using mysql 5.6

Upvotes: 1

Views: 35

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

I would use an inequality directly with the date column here:

SELECT SUM(Number1 - Number2) AS TOTAL 
FROM myTable 
WHERE Date >= '2019-01-01' AND Date < '2020-02-01';

This approach is clean, and also leaves the WHERE clause sargable, meaning that an index on Date can be used. If you really want to stick with using YEAR and MONTH, then use:

SELECT SUM(Number1 - Number2) AS TOTAL 
FROM myTable 
WHERE YEAR(Date) = 2019 OR YEAR(Date) = 2020 AND MONTH(Date) = 1;

Upvotes: 2

Related Questions