Summing without grouping
-
I am trying to sum an amount field, but when I try to enter the sum function, sql wants me to group by the field I am summing. If I do a group by on that field, I will get multiple records instead of 1 sum record. Here is my example...
Table - Erika_test_claim_billed
childid parentid checkdate docid billamt payamt
234 222 7/29/2005 12345 100 50
235 555 7/29/2005 55441 250 200
Table 2 - Erika_test_adjustments
docid child1_adj child2_adj child3_adj seq
12345 10 10 1
12345 20 2
55441 13 1
55441 14 2
55441 15 20 10 1
55441 16 10 1
55441 17 20 1
Here is the code I am using to link the data:
SELECT
dbo.Erika_test_claim_billed.childid,
dbo.Erika_test_claim_billed.parentid,
dbo.Erika_test_claim_billed.checkdate,
dbo.Erika_test_claim_billed.docid,
dbo.padfieldleft(case
when (dbo.Erika_test_adjustments.seq = 1 or
dbo.Erika_test_adjustments.seq is null) then
(case
when (dbo.Erika_test_claim_billed.billamt) < 0 then ltrim('(' + ltrim(cast(dbo.Erika_test_claim_billed.billamt*-1 as char(08))) + ')')
else
cast(dbo.Erika_test_claim_billed.billamt as char(10))
end)
else convert(char(10),'')
end,'',10) as fixed_length
FROM dbo.Erika_test_adjustments RIGHT OUTER JOIN
dbo.Erika_test_claim_billed ON dbo.Erika_test_adjustments.docid = dbo.Erika_test_claim_billed.docid
Here is the result set...
234 222 2005-07-29 00:00:00.000 12345 100.00
234 222 2005-07-29 00:00:00.000 12345
235 555 2005-07-29 00:00:00.000 55441 250.00
235 555 2005-07-29 00:00:00.000 55441
235 555 2005-07-29 00:00:00.000 55441 250.00
235 555 2005-07-29 00:00:00.000 55441 250.00
235 555 2005-07-29 00:00:00.000 55441 250.00
What I really want to do is sum the billamt with a seq of 1, but when I try to add the sum function to that field, it tells me I have to group the billamt. If I do that (in the cases where the billamt is different, it will create a seperate record for each amt and I want the data to display like this...
234 222 2005-07-29 00:00:00.000 12345 100.00
235 555 2005-07-29 00:00:00.000 55441 1000.00
Any ideas - I would really appreciate the help
-
One quick solution is as follows:
SELECT childid, parentid, checkdate, docid, sum(cast(fixed_length as dec(10,2)) as sumfixedlength
from (
SELECT
dbo.Erika_test_claim_billed.childid,
dbo.Erika_test_claim_billed.parentid,
dbo.Erika_test_claim_billed.checkdate,
dbo.Erika_test_claim_billed.docid,
dbo.padfieldleft(case
when (dbo.Erika_test_adjustments.seq = 1 or
dbo.Erika_test_adjustments.seq is null) then
(case
when (dbo.Erika_test_claim_billed.billamt) < 0 then ltrim('(' + ltrim(cast(dbo.Erika_test_claim_billed.billamt*-1 as char(08))) + ')')
else
cast(dbo.Erika_test_claim_billed.billamt as char(10))
end)
else convert(char(10),'')
end,'',10) as fixed_length
FROM dbo.Erika_test_adjustments RIGHT OUTER JOIN
dbo.Erika_test_claim_billed ON dbo.Erika_test_adjustments.docid = dbo.Erika_test_claim_billed.docid
) AS a
order by childid, parentid, checkdate, docid
Kindest Regards,David Rowland
-
I haven't tested it, but I think something like the following might work:
SELECT
dbo.Erika_test_claim_billed.childid,
dbo.Erika_test_claim_billed.parentid,
dbo.Erika_test_claim_billed.checkdate,
dbo.Erika_test_claim_billed.docid,
sum(case when dbo.Erika_test_adjustments.seq = 1 then dbo.Erika_test_claim_billed.billamt else 0 end)
FROM
dbo.Erika_test_adjustments RIGHT OUTER JOIN
dbo.Erika_test_claim_billed ON dbo.Erika_test_adjustments.docid = dbo.Erika_test_claim_billed.docid
group by
dbo.Erika_test_claim_billed.childid,
dbo.Erika_test_claim_billed.parentid,
dbo.Erika_test_claim_billed.checkdate,
dbo.Erika_test_claim_billed.docid
-
Thank you both so much for your help! With a combination of both of your efforts, as well as a co-worker here, I believe we have a winner
Here is the working code (for future frustrated programmers)...
SELECT
GroupSet.childid,
GroupSet.parentid,
GroupSet.checkdate,
GroupSet.docid,
dbo.padfieldleft(
(case
when (GroupSet.billamt) < 0 then ltrim('(' + ltrim(cast(GroupSet.billamt*-1 as char(08))) + ')')
else
cast(GroupSet.billamt as char(10))
end)
,'',10)+
dbo.padfieldleft(
(case
when (GroupSet.payamt) < 0 then ltrim('(' + ltrim(cast(GroupSet.payamt*-1 as char(08))) + ')')
else
cast(GroupSet.payamt as char(10))
end)
,'',10) as fixed_length
FROM
(
SELECT
dbo.Erika_test_claim_billed.childid,
dbo.Erika_test_claim_billed.parentid,
dbo.Erika_test_claim_billed.checkdate,
dbo.Erika_test_claim_billed.docid,
sum(case when dbo.Erika_test_adjustments.seq = 1 then dbo.Erika_test_claim_billed.billamt else 0 end) billamt,
sum(case when dbo.Erika_test_adjustments.seq = 1 then dbo.Erika_test_claim_billed.payamt else 0 end) payamt
FROM
dbo.Erika_test_adjustments RIGHT OUTER JOIN
dbo.Erika_test_claim_billed ON dbo.Erika_test_adjustments.docid = dbo.Erika_test_claim_billed.docid
group by
dbo.Erika_test_claim_billed.childid,
dbo.Erika_test_claim_billed.parentid,
dbo.Erika_test_claim_billed.checkdate,
dbo.Erika_test_claim_billed.docid) AS GroupSet
With the results I wanted...
234 222 2005-07-29 00:00:00.000 12345 100.00 50.00
235 555 2005-07-29 00:00:00.000 55441 1000.00 800.00
Till next time...
Erika
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply