Solved

SELECT Statement with Group by

Posted on 2011-09-12
3
304 Views
Last Modified: 2012-05-12
3 Tables to produce sum of Allowance_Or_Deduction_Amount per Employee_Id

The 1st table include Employee_details
Employee_Id
Employee_Name

Open in new window


The 2nd table include Allowances_Or_Deductions_Details
Allowance_Or_Deduction_Id
Allowance_Or_Deduction_Name

Open in new window


The 3rd table include Salaries details
Employee_Id
Allowance_Or_Deduction_Id
Allowance_Or_Deduction_Amount
From_Date
To_Date

Open in new window


Now I need to generate new query implement sum of each Allowance_Or_Deduction_Id per Employee_Id
The result as following: -
Employee_Id
Employee_Name
Allowance_Or_Deduction_Id
Allowance_Or_Deduction_Name
Sum of Allowance_Or_Deduction_Amount
From_Date (Start date of calculation)
To_Date (End date of calculation)

Open in new window


Also select statement can be using where to determine the start date and end date for implement calculation
0
Comment
Question by:egovernment
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36524293
What part do you not understand, the JOINs? And is this MS Access or SQL Server?

e.g.,
Salaries s
JOIN Allowances_Or_Deductions_Details  dd
   ON dd.Allowance_Or_Deduction_Id = s.Allowance_Or_Deduction_Id

As your title implies, you can use GROUP BY with SUM() to get aggregate you desire. Please post what you have tried and we can help you through tweaking it.
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36524337
try this.

SELECT
Sd.Employee_Id,
Ed.Employee_Name,
Sd.Allowance_Or_Deduction_Id,
AD.Allowance_Or_Deduction_Name,
Sum(Allowance_Or_Deduction_Amount),
FROM Employee_Details ed
	INNER JOIN Salaries_details Sd
	ON Sd.Employee_id = ed.Employee_Id
	INNER JOIN Allowances_Or_Deductions_Details  AD
	ON Sd.Allowance_Or_Deduction_Id = AD.Allowance_Or_Deduction_Id
WHERE 
Sd.From_Date >= 'your initial date'
and Sd.To_Date <= 'you final date'
GROUP BY
Sd.Employee_Id,
Ed.Employee_Name,
Sd.Allowance_Or_Deduction_Id,
AD.Allowance_Or_Deduction_Name

Open in new window

0
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 500 total points
ID: 36529047
@Crashman: your SQL sums only the details that are fully included between the start-end date.

If Allowance_Or_Deduction_Amount are eligible when they are crossing the selected timeframe, then the SQL should be slightly different.
SELECT
Sd.Employee_Id,
Ed.Employee_Name,
Sd.Allowance_Or_Deduction_Id,
AD.Allowance_Or_Deduction_Name,
Sum(Allowance_Or_Deduction_Amount),
FROM Employee_Details ed
	INNER JOIN Salaries_details Sd
	ON Sd.Employee_id = ed.Employee_Id
	INNER JOIN Allowances_Or_Deductions_Details  AD
	ON Sd.Allowance_Or_Deduction_Id = AD.Allowance_Or_Deduction_Id
WHERE 
Sd.To_Date >= 'your initial date'
and Sd.From_Date <= 'you final date'
GROUP BY
Sd.Employee_Id,
Ed.Employee_Name,
Sd.Allowance_Or_Deduction_Id,
AD.Allowance_Or_Deduction_Name

Open in new window


It differs from Crashman only on the WHERE clause
   Sd.To_Date >= 'your initial date' and Sd.From_Date <= 'you final date'
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Read about achieving the basic levels of HRIS security in the workplace.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now