CODEWITHSUNDEEP
mysqlsql
Jaeger
Jaeger

Reputation: 1754

Get a sum of specific cells under condition

I'm convinced that what I want to do is obvious, but I've been stuck on it for an hour.

Here is a simplified version of my database:

+------------+---------+----------+---------+
|  EXERCICE  |   MCOT  |  MCOT_1  |  MCOT_2 |
+------------+---------+----------+---------+
|       2016 | 1754.00 |  1385.00 |  776.00 |
|       2016 | 3166.00 | 14291.00 |  582.00 |
|       2015 | 1385.00 |     0.00 | 5820.00 |
|       2016 |    0.00 |  4551.00 | 5969.00 |
|       2014 |  792.00 |  3364.00 | 2522.00 |
+------------+---------+----------+---------+

All I'm trying to do is to be able to sum in a way or another of each columns (MCOT, MCOT_1, MCOT_2) together as long as the exercice year is the same

My desired output is the following:

+------------+--------------+---------------+
|  max_year  |   last_year  |  year_before  |
+------------+--------------+---------------+
|   32474    |     7205     |     6678      |
+------------+--------------+---------------+

I tried a few things like 

SELECT MCOT + MCOT_1 + MCOT_2 AS max_year WHERE EXERCICE = MAX(EXERCICE) FROM TABLE

and

SELECT SUM(CASE WHEN EXERCICE = MAX(EXERCICE) THEN MCOT + MCOT_1 + MCOT_2 END) 
AS max_year FROM table

I guess I'm doing this terribly but I thought it would be easier than this.

How should I do this?

Upvotes: 0

Views: 35

Answers (1)

Milan Chheda
Milan Chheda

Reputation: 8249

It should be SELECT, FROM, TABLE and then the WHERE condition, and in order to get sum of columns, you should use SUM():

SELECT SUM(MCOT + MCOT_1 + MCOT_2) AS Total
FROM your_table
WHERE EXERCICE = (SELECT MAX(EXERCISE) AS EXERCISE FROM your_table);

Upvotes: 1

Related Questions