filter duplicates - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

filter duplicates

Author

Topic

sconard
Starting Member

18 Posts

Posted - 2011-08-07 : 21:39:54

I am trying to pull records from one table(a) and insert into another (b). Table a has duplicates in two fields that I must filter to distinct values while performing calculations on another field (3). Which of the field4 values is not important. So...

a table has
field1 field2 field3 field4
034 01 9 v
034 01 12 c
035 04 3 s
035 04 5 q
036 01 3 x
037 09 2 r

I want to sum all records that have dup values in field1 and field2 so table b will have one record for each distinct of field1 and field2

b table has insert of
field1 field2 field3 field4
034 01 21 v
035 04 8 s
036 01 3 x
037 09 2 r

I have looked at a number of duplicate filtering queries but cannot seem to find the one.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-07 : 21:54:46

You should be able to use a simple group by clause like this:

INSERT INTO NewTable (field1,field2,field3,field4)
SELECT
   field1,
   field2,
   SUM(field3),
   MAX(field4)
FROM
   OldTable
GROUP BY
   field1,
   field2;

sconard
Starting Member

18 Posts

Posted - 2011-08-08 : 08:20:09

example is actually missing 9 other fields that must be captured and is not a part of group by so I get "field is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

calculations are made on two fields by functions

INSERT INTO [StagingClient].[dbo].[TimesheetTransferDocumentDetail]
([TransactionDate]
,[ProjectCode]
,[PhaseCode]
,[CompanyCode]
,[TaskCode]
,[OrganizationCode]
,[EmployeeCode]
,[BillingClassification]
,[HoursAmt]
,[EffortAmtProjectCurrency]
,[CostAmtEmployeeCurrency])

select
'9/10/2011' as 'TransactionDate'
,s.TIMPROJ as 'ProjectCode'
,s.timlev2 as 'PhaseCode'
,'WRA' as 'CompanyCode'
,s.TIMLEV3 as 'TaskCode'
,dbo.getOrgCode(s.TIMPROJ) as 'OrganizationCode'
,'******' as 'EmployeeCode'
,'N' as 'BillingClassification'
,0 as 'HoursAmt'
,stagingClient.dbo.getl2lfee(s.TIMPROJ,s.TIMLEV2) as 'EffortAmtProjectCurrency'
,stagingClient.dbo.getl2lcost(s.TIMPROJ,s.TIMLEV2) as 'CostAmtEmployeeCurrency'

from semaphore.dbo.[time] s
group by timProj,timLev2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources