Select multiple distinct fields, and summing another field
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
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
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...)
精彩评论