SQL Query - Counts & Sums

Transact-SQL
1

I have this Query I used to find my totals

Select Distinct
Count(distinct [clm_id1]) as [Claim Number],
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(clm_sppo AS MONEY)) AS TotalSavings,
SUM(CAST(clm_nego AS MONEY)) AS [Negotiated Allowed Charges]

However, there are times that there could be in the data that there could be 3 claim numbers, but the totals are the same. I just want the totals to add once not all three times.

Example

Claim 2123 - 10
Claim 2123 - 10
Claim 2123 - 10

My query pulls

Claim count 1 (This part works for me)
Total Charges 30, but that is wrong, I need it to be 10

How can I make the charges be 10 and not 30?

2

Try something like shown below. When there are 3 rows for a given claim number and you want to pick only one, is there some criterion you can/have to use to decide which of those 3 you want to pick? If there is, replace the (SELECT NULL) with the columns that determine the ordering criteria, such that, when ordered by those columns, the row you want will be at the top.

;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY clm_id1 ORDER BY (SELECT NULL)) AS RN
	FROM
		YourTable
)
SELECT
    COUNT(DISTINCT [clm_id1]) AS [Claim Number] ,
    SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges ,
    SUM(CAST(clm_sppo AS MONEY)) AS TotalSavings ,
    SUM(CAST(clm_nego AS MONEY)) AS [Negotiated Allowed Charges]
FROM
	cte
WHERE
	RN = 1;
3

Thank you, this worked for me!