Thursday, February 25, 2010

SQL Group by With Joins

Shout it kick it on DotNetKicks.com
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= SalesEmployeeIDgroup 
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,TotalSalesfrom 
Employee inner join 
     (Select SalesEmployeeID,sum(SalesTotal) as TotalSales 
                        from Sales group by SalesEmployeeID) empSales 
on empSales.SalesEmployeeID= EmployeeID

1 comment:

  1. Consider changing the title to include the word "Subquery"

    ReplyDelete