Sum function and distinct values

[Veejc]

Hi All

Here is the Query I am running.

select distinct cast(rx_all.memberid as varchar) + cast(rx_all.dispdate as varchar)+ cast(rx_all.ndccode as varchar) as distinct_rx_claim ,
substring(convert( varchar,rx_all.Dispdate, 112), 5,2)+ '/' +right(convert( varchar,rx_all.Dispdate, 112), 2) + '/' + left(convert( varchar,rx_all.Dispdate, 112), 4),
cur.unique_id,/* this is the unique indicator*/
rx_all.dayssupply,
case rx_all.otc
when 'F' then 'N'
when 'O' then 'Y'
end as supply ,
sum(rx_all.ingcost) as PDiscIng,
0 as PDisR,/* supposed to be PDISR, there is no mapping */
sum(rx_all.proffee) as PDispFee,
/* no mapping for padmfee, just says 0 is this right? */
0 as PAdmFee,
sum(rx_all.copay) as PMemberCo,
/*(select sum(totamt) from pha.dbo.tbl_paidclaims where memberid = rx_all.memberid
and dispdate = rx_all.dispdate
and ndccode = rx_all.ndccode),*/
sum(rx_paid.totamt) as TotalCost,--rx_all.totamt,
left(rx_all.NDCCode,5) + substring(rx_all.NDCCode, 7,4) + right(rx_all.NDCCode, 2) as NDC
from pha.dbo.tbl_allclaims rx_all left outer join maui.nhpri_diam_ds01.dbo.TBL_CurrentMemberDimension cur
on ltrim(rtrim(rx_all.subsid )) + ltrim(rtrim(rx_all.persno)) = ltrim(rtrim(cur.aasubno)) + ltrim(rtrim(cur.aapersno))
left outer join pha.dbo.tbl_paidclaims rx_paid on
cast(rx_all.memberid as varchar) + cast(rx_all.dispdate as varchar)+ cast(rx_all.ndccode as varchar) =
cast(rx_Paid.memberid as varchar) + cast(rx_paid.dispdate as varchar)+ cast(rx_paid.ndccode as varchar)
where rx_all.dispdate between '01/01/2005' and '01/06/2005'
group by rx_all.memberid,
cur.unique_id,
rx_all.dispdate,
rx_all.ndccode,
rx_all.dayssupply,
rx_all.otc,
rx_all.ingcost,
rx_all.proffee,
rx_all.copay,
rx_all.status

here is a portion of the results from that query.
01/03/2005 1378 15 Y -22.03 0 -2.00
01/03/2005 1378 15 Y 22.03 0 2.00

My goal is to get these two lines combined summing on the values (22.03) and (2.00)... the total should be 0 if the two rows are summed.

All other fields returned are the same for these two records, I made sure of that. The -22.03 and 22.03 are the only differences. I want those two combined, though.

What am I doing wrong?

 

[MartinCroft]

Hi

Going by the code your first column is unique is this correct and you are grouping by it, therefore you wont ever get the two rows summed if thats what you want. If you didn't have the unique column, the sum should return the summed value, if you then wanted a unique column you could do this in a derived table after you had summed the values


SELECT
uniqueCol
,a
,b
,c
FROM
(
SELECT
a
,b
,sum(c)
FROM
table
GROUP BY
a,b
)table