65.9K
CodeProject is changing. Read more.
Home

SQL Group by With Joins

starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.92/5 (15 votes)

Feb 9, 2010

CPOL
viewsIcon

188919

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
EmployeeContactNo
Structure of Sales:
SalesID
SalesEmployeeID
SalesDate
SalesTotal
Now 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,SalesTotal
But 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