skip to Main Content
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


  1. 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
    
    Login or Signup to reply.
  2. SQL query that selects one user with the maximum value of prince per year, achieving your desired result:

    SELECT e.first_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 year
    HAVING SUM(e.prince) = (
        SELECT MAX(SUM(e.prince))
        FROM (
            SELECT * FROM employee WHERE termination IS NULL
        ) e
        JOIN storage s ON e.id = s.eid
        GROUP BY YEAR(e.hire_date)
    )
    ORDER BY year;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search