Solved

SELECT Statement with Group by

Posted on 2011-09-12
3
308 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 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