• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

SELECT Statement with Group by

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
egovernment
Asked:
egovernment
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
CrashmanCommented:
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
 
BigSchmuhCommented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now