SQL Group by With Joins






3.92/5 (15 votes)
How to use Group By clause when joining to table Let's consider one scenario where I have two table employees (contains employee detail) and sales (contains infomation about sales done by employee).Structure of...
How to use Group By clause when joining to table
Let's consider one scenario where I have two table employees (contains employee detail) and sales (contains infomation about sales done by employee).
Structure of Employee:
EmployeeID EmployeeFirstName EmployeeLastName EmployeeEmailID EmployeeContactNoStructure of Sales:
SalesID SalesEmployeeID SalesDate SalesTotalNow I want to get total sales done by employee with employee name. For that, I write a query like:
Select EmployeeFirstName,EmployeeLastName,sum(SalesTotal) from Employee inner join Sales on EmployeeID= SalesEmployeeID group by EmployeeFirstName,EmployeeLastName,SalesTotalBut there is one problem in the above Query. I have to add two more extra fields in group by clause which make query inefficient and make no sense logically So the solution for this is to use derive table which makes sense logically and clears query
Select EmployeeID,EmployeeFirstName,EmployeeLastName,TotalSales from Employee inner join (Select SalesEmployeeID,sum(SalesTotal) as TotalSales from Sales group by SalesEmployeeID) empSales on empSales.SalesEmployeeID= EmployeeID