How to filter the result Mysql

Question

Hard to describe what i want, but I will try: First of all, i have 2 tables: employee and salary. I joined 2 tables by this SQL command:

"SELECT employee.id, employee.employee_name, employee.image, salary.year, salary.month, salary.day, salary.total_salary, salary.id, salary.created
                        FROM employee
                 JOIN salary ON salary.employee_id=employee.id"

to get all value i inserted, after execute, i got:

Created              name    year     month   day(working)       total_salary
------------------------------------------------------------------------------
2016-12-17 20:41      A      2016       1         31              1550000
2016-12-17 21:49      A      2016       1         20              2000000
2016-12-17 22:49      A      2016       2         20              2000000
2016-12-18 22:43      B      2016       2         15              1550000
2016-12-18 23:43      B      2016       2         10              1000000
2016-12-18 23:49      B      2016       2         1                100000

After that, i want to get the newest value per month, so i add this command to the end of sql above:

 GROUP BY employee.employee_name, salary.year, salary.month"

and got this:

Created              name    year     month   day(working)       total_salary
------------------------------------------------------------------------------
2016-12-17 20:41      A      2016       1         31              1550000
2016-12-17 22:49      A      2016       2         20              2000000
2016-12-18 22:43      B      2016       2         15              1550000

What query will return the following result?

Created              name    year     month   day(working)       total_salary
------------------------------------------------------------------------------
2016-12-17 21:49      A      2016       1         20              2000000
2016-12-17 22:49      A      2016       2         20              2000000
2016-12-18 23:49      B      2016       2         1                100000

I'm a bit confused, you want to group them by `month` and yet in your desired output there are 2 of the same `month` that seems contradictory. — Mihailo, Dec 17 '16 at 19:04
you can see each of Employee have many Month, i i just want to display each month of a employee. Seems a little bit complex :)) — trungducng, Dec 17 '16 at 20:46
If you put actual tables it would be more helpful to test query. For the absence of that I can say that you just write subquery for calculating total for each employee and then join it to main table. — a-man, Dec 18 '16 at 02:00

score 1 · Answer 1 · edited Aug 14 '20 at 15:28

Before you group you should sort the entries by created date the newest entries are first:

ORDER BY salary.created DESC

Change the first query (you have to remove or alias one of the id's because you will have two columns with the same name):

SELECT employee.id as employee_id, employee.employee_name, employee.image, salary.year, salary.month, salary.day, salary.total_salary, salary.id, salary.created 
FROM employee JOIN salary ON salary.employee_id=employee.id 
ORDER BY salary.created DESC

So the last created are shown first. Then group the results like you did:

SELECT * FROM (
    SELECT employee.id as employee_id, employee.employee_name, employee.image, salary.year, salary.month, salary.day, salary.total_salary, salary.id, salary.created
    FROM employee
    JOIN salary ON salary.employee_id=employee.id 
    ORDER BY salary.created DESC 
) as employee_salary 
GROUP BY employee_salary.employee_name, employee_salary.year, employee_salary.month

Thanks for ur help but it still don't work for me :( , the result still return the same as i described above — trungducng, Dec 17 '16 at 20:48
Can you send me a dump of those 2 tables? Or just some example data i can play with? I created 2 tables and with my query i got the result i think you wanted... — Stijn Duynslaeger - Echron, Dec 19 '16 at 17:42

score 1 · Answer 2 · edited May 23 '17 at 12:00

My starting point was after your joins, I just made a table that has the data as your first result.

With this query:

SELECT created, name, year, month, day, total_salary FROM 
( SELECT * FROM employees
  ORDER BY created DESC
) AS sub
GROUP BY name, month, year;

I've got the results you asked for:

Created              name    year     month   day(working)       total_salary
------------------------------------------------------------------------------
2016-12-17 21:49      A      2016       1         20              2000000
2016-12-17 22:49      A      2016       2         20              2000000
2016-12-18 23:49      B      2016       2         1                100000

Basically the trick was to handle the ordering before you pack them up into groups with GROUP BY
So I made a subQuery that just handles the ordering. This post helped me figure out how to do this.

Here's a Fiddle where you can play around and adjust it to your starting query.

How to filter the result Mysql

2 Answers2