How To Select data from multiple tables with grouping for duplicates-CodePudding

I have Two Tables, one with Employees Details and another with vacations taken by them in different years.Please check this image for the tables

Here as you'll find out in the vacation table, for the same employee with same employeeId and in sam year different vacation days are mentioned. Like John Smith in 2011 have two entries one with 10 vacation and one with 3 vacation. I want my query to return a single row with vacations mentioned as 13.

I tried the following query but no luck

SELECT Employee_Details.EmployeeId, Employee_Details.EmployeeName, Employees_Vacation.Year, Employees_Vacation.Vacation, Employee_Details.Department
FROM Employees_Vacation INNER JOIN Employee_Details ON Employees_Vacation.EmployeeId=Employee_Details.EmployeeId group by Employee_Details.EmployeeId ORDER BY Employee_Details.EmployeeName, Employees_Vacation.Year ;

CodePudding user response：

if i understood you right, i think this may help you

select sum(vacation) as sum, ev.year, ed.EmployeeName from employee_Details as ed inner join employee_Vacation as ev
on ed.employeeID = ev.employeeID 
group by ev.year, ed.EmployeeName

CodePudding user response：

A lot here will depend on the sql engine you are using, however there are some things that will apply regardless of the engine to consider:

Your current GROUP BY clause is grouping only by employeeId - from the question text it seems like you are instead looking for results grouped by employee AND vacation year
Your projection (SELECT statement) currently isn't actually aggregating anything - it's just projecting a bunch of fields. On some db engines, this actually isn't even allowed (SQL Server for example will only allow grouped or aggregated columns in the projection). Again, from the question text it seems like you are looking for the SUM of vacation days per employee and year.

Taking these into account and assuming the assumptions made are accurate, something like the following should work in most/all modern RDBMS's:

SELECT Employee_Details.EmployeeId,
       Employee_Details.EmployeeName,
       Employees_Vacation.Year,
       SUM(Employees_Vacation.Vacation) AS TotalVacationDays,
       Employee_Details.Department
FROM Employees_Vacation
INNER JOIN Employee_Details
ON Employees_Vacation.EmployeeId = Employee_Details.EmployeeId
GROUP BY
    Employee_Details.EmployeeId, Employee_Details.EmployeeName,
    Employees_Vacation.Year, Employee_Details.Department
ORDER BY
    Employee_Details.EmployeeName,
    Employee_Details.EmployeeId,
    Employees_Vacation.Year;

You may be able to get away with fewer grouping clauses in some engines (MySql for example). Additionally I added an EmployeeId to the order by clause to ensure records for the same employee remain together in the results (for employees with the same names for example).