SQL sum with multiple joins

What's wrong? Can you post data, current results, and expected results?

>however I'm not getting the correct result.
Just a thought ... since experts here cannot connect to your data source, run queries, and know what should be the 'correct result', you need to define 'correct result' for us.  More helpful would be sample data sets both before the query, what is being returned in the set, and what you believe 'correct' is.

Sorry for not being more clear.

The IV00101 table is a list of all items that are available to sell (excluding the exceptions noted in the where statement)

The iv00102 table has each item with multiple locations (locncode) from the iv00101 table and also has the primary vendor id (primvndr) which is then used to join to the pm00200 that contains the vendor name (vndname).

The sop10200_sop30300_view is a table of all orders and the items sold with the XTNDPRCE = to the total for that item on each order.

The sop10100_sop30200_union is the order header table that contains the document date (dockdate) joined to the sop10200_sop30300_union table on order number (sopnumbe).

What I'm attempting to produce is a list of all the items in IV00101 (less the exclusions) with their primary vendor (primvndr) and the total (XTNDPRCE)l if any, on the parts sold in the last  12 months.

The problem is that when I run the query I'm getting the correct details and format but when i place in a spreadsheet and sum the ExtendedPrice results I get like $750,123,123 instead of something like $53,123,123.

I've attached an example of what I'm might be getting vs what it should be.

I hope this makes more sense

is that just because you would need to replace

 ,isnull(sum(sop2.xtndprce),0) ExtendedPrice

Select allOpen in new window

with
 

,sum(isnull(sop2.xtndprce,0)) ExtendedPrice

Select allOpen in new window

Result is the same

can we get scripts to generate tables and data? You didn't attached your example.

Sorry forgot to attach
Workbook1.xlsx

where is your data to give these results?

Where @itemnmbr and @primvndr are the itemnmbr and primvndr (respectively) of the record that you are examining:

Try to debug the query.  Are you getting multiple records where you are not expecting?  Does the results of the query below look consistent with what you are looking for?

select 
	iv1.itemnmbr Itemnmbr, 
	max(iv1.itemdesc)      ItemDescription
      ,max(iv1.uscatvls_2) Category
      ,iv2.primvndr VendorID
      ,isnull(max(pm2.vendname),'') VendorName
      , isnull(max(sop2.unitprce),0) UnitPrice
      ,sop2.xtndprce ExtendedPrice
      , isnull(max(sop2.unitcost),max(iv1.currcost)) ItemCost
	  , sum(1) rec_ct
from 
	iv00101 iv1 (nolock)
	left join iv00102 iv2  (nolock) on iv2.itemnmbr=iv1.itemnmbr and iv2.locncode='COMPANY'
	left join pm00200 pm2  (nolock) on pm2.vendorid=iv2.primvndr
	left join sop10200_sop30300_union sop2  (nolock) on sop2.itemnmbr=iv1.itemnmbr
	left join sop10100_sop30200_union sop1  (nolock) on sop1.sopnumbe=sop2.sopnumbe and sop1.docdate >=getdate()-365
where 
	iv1.itemtype<>'2'
	and iv1.itmclscd<>'NON-IV'
	and iv1.itemcode='ACTIVE_ALL'
	and iv1.itemnmbr = @itemnmbr 
	and iv2.primvndr = @primvndr 
group by iv2.primvndr, iv1.itemnmbr, sop2.xtndprce

Select allOpen in new window