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 |
+--------------+---------------+------+
You can use ROW_NUMBER with a CTE
WITH CTE as (
SELECT e.frist_name, SUM(e.prince) sum_ , YEAR(e.hire_date) AS year
, ROW_NUMBER() OVER(PARTITION BY YEAR(e.hire_date) ORDER BY SUM(e.prince) DESC) rn
FROM (
SELECT * FROM employee WHERE termination IS NULL
) e
JOIN storage s ON e.id = s.eid
GROUP BY e.id,e.frist_name, YEAR(e.hire_date) )
SELECT
frist_name, sum_, year
FROM CTE
WHERE rn = 1