SELECT e.frist_name, SUM(e.prince), YEAR(e.hire_date) AS year FROM (
SELECT * FROM employee WHERE termination IS NULL
) e
JOIN storage s ON e.id = s.eid GROUP BY e.id, e.hire_date;
After SQL query I have a table like this:
+--------------+---------------+------+
| e.first_name | SUM(e.prince) | year |
+--------------+---------------+------+
| Johnn | 1450 | 2012 |
+--------------+---------------+------+
| Emma | 1020 | 2013 |
+--------------+---------------+------+
| Gordon | 340 | 2012 |
+--------------+---------------+------+
| Ave | 600 | 2014 |
+--------------+---------------+------+
My desired result is something like this (only one user per year with max value of prince):
+--------------+---------------+------+
| e.first_name | SUM(e.prince) | year |
+--------------+---------------+------+
| Johnn | 1450 | 2012 |
+--------------+---------------+------+
| Emma | 1020 | 2013 |
+--------------+---------------+------+
| Ave | 600 | 2014 |
+--------------+---------------+------+
2
Answers
You can use ROW_NUMBER with a CTE
SQL query that selects one user with the maximum value of prince per year, achieving your desired result: