SQLTeam.com | Weblogs | Forums

Total a field based on another field

#1

I have the following data. The raw data changes week to week so I need the query to be able to calculate the new data.

John Smith Annual 9.75
John Smith Annual 3.25
John Smith Holiday 6.00
John Smith Holiday 2.00
John Smith OT 5.75

How do I code SQL to make it look like this?

John Smith Annual 13.00
John Smiht Holiday 8.00
John Smith OT 5.75

Any suggestions will be apprecialted.

#2
create table #rykey(FirstName varchar(50), LastName varchar(50), 
TimeType Varchar(50), days float)

insert into #rykey
select 'John', 'Smith', 'Annual', 9.75   union 
select 'John', 'Smith', 'Annual', 3.25	 union 
select 'John', 'Smith', 'Holiday', 6.00	 union 
select 'John', 'Smith', 'Holiday', 2.00	 union 
select 'John', 'Smith', 'OT', 5.75		 


--this part is for you 
select FirstName, LastName, TimeType, sum(days)
  from #rykey
  group by FirstName, LastName, TimeType
--this part is for you 

drop table #rykey
#3

Thanks...this will work but I failed to mention that there are several hundred employees and about 6000 lines of data. How do I populate all of the data into the table without manually inserting each record?

#4

That is a just sample table just to demonstrate how it works on my side because I don't have access of your server :slight_smile:

Also notice I point out the part you use. so replace the part that says #rykey with your table

so in case my solution is not functional or there is a better way of doing it others can pick it up where I left off and try it out.