Solved

SELECT Statement with Group by

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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