Solved

SELECT Statement with Group by

Posted on 2011-09-12
3
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 60

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

737 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