Click here to Skip to main content
Click here to Skip to main content

SQL Group by With Joins

, 15 Feb 2010
Rate this:
Please Sign up or sign in to vote.
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)

About the Author

Pranay Rana
Software Developer (Senior) GMind Solusion
India India

Microsoft C# MVP (12-13)

 
Hey, I am Pranay Rana, working as a ITA in 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:


Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberehsabd10-Jul-13 9:00 
Generaland also approach of first query is better in terms of perfo... PinmemberRahil Jan Muhammad26-Feb-12 14:42 
General1 >> why you have SalesTotal in group by of first query??? PinmemberRahil Jan Muhammad26-Feb-12 14:41 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 16 Feb 2010
Article Copyright 2010 by Pranay Rana
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid