Click here to Skip to main content
15,881,882 members
Articles / Web Development / ASP.NET
Tip/Trick

SQL Group by With Joins

Rate me:
Please Sign up or sign in to vote.
3.92/5 (16 votes)
15 Feb 2010CPOL 187.3K   8   6
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India

Microsoft C# MVP (12-13)



Hey, I am Pranay Rana, working as a Team Leadin MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.

For me def. of programming is : Programming is something that you do once and that get used by multiple for many years

You can visit my blog


StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr

Awards:



Comments and Discussions

 
QuestionSolve my problem Pin
Member 1187489630-Jul-15 6:53
Member 1187489630-Jul-15 6:53 
QuestionRe: Solve my problem Pin
Member 159305157-Mar-23 23:31
Member 159305157-Mar-23 23:31 
GeneralMy vote of 5 Pin
ehsabd10-Jul-13 9:00
ehsabd10-Jul-13 9:00 
Generaland also approach of first query is better in terms of perfo... Pin
Raheel1234526-Feb-12 14:42
Raheel1234526-Feb-12 14:42 
General1 >> why you have SalesTotal in group by of first query??? Pin
Raheel1234526-Feb-12 14:41
Raheel1234526-Feb-12 14:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.