Summarizing One Table Based on Another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Summarizing One Table Based on Another Table

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-01-29 : 17:58:14

I have a select statement that produces the following table:

SELECT * FROM EARNINGS

EARNINGS TABLE:

CODE|DESCRIPTION |AMOUNT|EMPLOYEE_ID
===============================================
REG|Regular Pay....|300.00|111111
VAC|Vacation Pay...|080.50|111111
DBL|Double Time....|133.00|111111
SAL|Salaried Wage..|400.00|111111
HLF|Time and a half|060.00|111111
HOL|Holiday Time...|070.00|111111
REG|Regular Pay....|459.75|222222
DBL|Double Time....|055.50|222222
VAC|Vacation Pay...|200.00|222222
HLF|Time and a half|044.00|222222

There is another table called CODE which contains the "CODE":

CODE|TYPE|DESCRIPTION
=================
DBL|O|Regular Pay....
HLF|O|Time and a half
HOL|O|Holiday Time...
REG|R|Regular Pay....
SAL|R|Salaried Wage..
VAC|V|Vacation Pay...

Another related table is called TYPE:

TYPE|DESCRIPTION
================
R|Regular
V|Vacation
O|Overtime

The one thing I want to point out is the relation between the code
and the type field. Some codes are of the same type, such as the
codes for overtime. What I want to do is create a SELECT statement
that will return the same information in the EARNINGS table,
but "summarize" it according to the type. It's also important to
keep employee id's distinct. So, the "result set" I'd like to see
from the above tables would look like this (notice that the description
which displays is the type description, not the code description):

TYPE|DESCRIPTION|AMOUNT|EMPLOYEE_ID
===============================================
R|Regular.......|700.00|111111
O|Overtime......|263.00|111111
V|Vacation......|080.50|111111
R|Regular.......|459.75|222222
O|Overtime......|099.50|222222
V|Vacation......|200.00|222222

singularity
Posting Yak Master

153 Posts

Posted - 2010-01-29 : 18:30:29

[code]
select c.type, c.description, sum(a.amount) as amount, a.employee_id
from earnings a
join code b on a.code = b.code
join type c on b.type = c.type
group by c.type, c.description, a.employee_id
[/code]

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-02-02 : 11:11:46

That worked very - thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources