Select multiple distinct fields, and summing another field

Question

I have a table where I have 6 columns. 5 of these columns I have to make sure that I don't have any duplicates. So I used the statement:

SELECT SUB_ACCT_NO_PAJ, CustomerType, POST_DTE_PAJ, IA_DateYear, ADJ_RSN_PAJ, count(*) AS [aCount]
INTO TempTable1
FROM All_Adjustments
GROUP BY SUB_ACCT_NO_PAJ, CustomerType, POST_DTE_PAJ, IA_DateYear, ADJ_RSN_PAJ, 

My problem is that the sixth column, POST_AMT_PAJ, needs to be totaled for each of these rows. So if any of the rows had been exact duplicates in the other fields, then I need to take each of the POST_AMT_PAJ from them and total them for the new table.

Any help is appreciated! Thanks!

Editing to demonstrate better

All Adjustments:

SUB_ACCT   |    Customer Type   |   POST_DTE   |  Dateyear   |  POST_AMT
------------------------------------------------------------------------
  1        |         R          |    July 3    |    2010     |     5
  1        |         R          |    July 3    |    2010     |     8
  2        |         L          |    June 2    |    2008     |    14  
  2        |         R          |    June 2    |    2009     |    12

Would go to...

SUB_ACCT   |    Customer Type   |   POST_DTE   |  Dateyear   |  POST_AMT
------------------------------------------------------------------------
  1        |         R          |    July 3    |    2010     |    13
  2        |         L          |    June 2    |    2008     |    14  
  2        |         R          |    June 2    |    2009     |    12

Answer 1

Try this:

SELECT 
 SUB_ACCT_NO_PAJ, 
 CustomerType, POST_DTE_PAJ, 
 IA_DateYear, 
 Sum(ADJ_RSN_PAJ) as 'Total_Adj_Rsn_Paj', 
 count(*) AS [aCount] 
INTO TempTable1 
FROM All_Adjustments 
GROUP BY SUB_ACCT_NO_PAJ, CustomerType, POST_DTE_PAJ, IA_DateYear

Since ADJ_RSN_PAJ would then be in an aggregate function, it should not cause your Group By clause to blow up.

NOTE: This is completely from memory, so you may need to do some tweeking (I don't actually have SQL Server I can play with- even for syntax checking...)

Answer 2

To get the total of a column, use SUM:

SELECT
    SUB_ACCT_NO_PAJ,
    CustomerType,
    POST_DTE_PAJ,
    IA_DateYear,
    ADJ_RSN_PAJ,
    SUM(POST_AMT_PAJ) AS POST_AMT_PAJ_total
    COUNT(*) AS [aCount]
INTO TempTable1
FROM All_Adjustments
GROUP BY
    SUB_ACCT_NO_PAJ,
    CustomerType,
    POST_DTE_PAJ,
    IA_DateYear,
    ADJ_RSN_PAJ