Sum(distinct)

[dolodolo]

Hi,

I am trying to sum a field that is joined to a table. The relationship is one(or two)-to-many. The join is a LEFT OUTER. When I sum the field, without a DISTINCT command, I get duplicate records. When I use SUM(DISTINCT sbudget) then it skips over records that have identical amounts. How can I:

Sum(sbudget) only once for each unique HMY?

Hope this makes sense. Any help is most appreciated.

 

[SQLBill]

Can you provide the table structure and sample data? Then based on the sample data, show us what results you want.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[dolodolo]

How do I attach an excel file?

 

[RyanEK]

You shouldn't need to attach a file. Just paste a subset of sample data and expected results directly.

 

[djj55]

Also what sql have you used so far?

 

[dolodolo]

Table 1 - CMRevision:

hmy hjob hcat camt dtdate hjd
18776 1327 2 (125,000.00) 1/3/07 40407
18777 1327 2 125,000.00 1/3/07 40407
18778 1327 2 (125,000.00) 12/31/06 40407
18779 1327 2 750,000.00 12/5/07 40407
18780 1327 2 (750,000.00) 12/12/07 40407
55928 1327 2 (10,000.00) 2/26/08 40407
58632 1327 2 20,000.00 4/4/08 40407
61304 1327 2 115,000.00 6/30/08 40407
61305 1327 2 115,000.00 6/30/08 40407
61651 1327 2 10.00 7/2/08 40407
55929 1328 2 10,000.00 2/26/08 44391

Table 2 Jobdetl:

hmy hjob hcat sbudget
40407 1327 2 10,000.00
44391 1328 2 10,000.00

DESIRED RESULTS:
Sbudget Camt
HCAT-2 20,000.00 125,010.00

 

[SQLSister]

Looks like a case for derived tables.
Create a query to sum the amounts in CMRevision
Create a query to sum the Jobdetl budget amounts
then use these as derived tables
something like

select total, budgetamount, category
from
(select category, sum (somefield) as total from table1 group by category) a
join 
(select category, sum (someotherfield) as budgetamount from table2 group by category) b
on a.category = b.category

"NOTHING is more important in a database than integrity." ESquared

 

[dolodolo]

Thanks I'll give that a try.