SQL sum with multiple joins

I'm attempting to sum the XTNDPRCE on an Sales detail view (ExtendedPrice) (SOP10200_ SOP30300_union) by itemnmbr with joins to multiple tables, however I'm not getting the correct result.

Below is the query I'm using but not sure how to make it work.



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
      ,isnull(sum(sop2.xtndprce),0) ExtendedPrice
      , isnull(max(sop2.unitcost),max(iv1.currcost)) ItemCost
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'
group by iv2.primvndr,iv1.itemnmbr
jdr0606Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
What's wrong? Can you post data, current results, and expected results?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
1
jdr0606Author Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Éric MoreauSenior .Net ConsultantCommented:
is that just because you would need to replace
 ,isnull(sum(sop2.xtndprce),0) ExtendedPrice

Open in new window

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

Open in new window

0
jdr0606Author Commented:
Result is the same
0
Éric MoreauSenior .Net ConsultantCommented:
can we get scripts to generate tables and data? You didn't attached your example.
0
jdr0606Author Commented:
Sorry forgot to attach
Workbook1.xlsx
0
Éric MoreauSenior .Net ConsultantCommented:
where is your data to give these results?
0
TONY TAYLORCommented:
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

Open in new window

0
Scott PletcherSenior DBACommented:
Since joins will throw off any SUM, you need to do the SUMs separately, in a derived table, then join that to the main query, as below.  That should also make the query much more efficient since you'll no longer need a GROUP BY on the main query.  If you still do need a MAX() function on the outer query, then you can also convert the read of that table to a derived table(s) or, less efficiently, add the GROUP BY back into the main query.  A MAX() won't get incorrect results because of extra rows like a SUM() will.


select iv1.itemnmbr Itemnmbr
      ,iv1.itemdesc
      ,iv1.uscatvls_2
      ,iv2.primvndr AS VendorID
      ,pm2.vendname AS VendorName
      ,isnull(sop2.unitprce, 0) AS UnitPrice
      ,isnull(sop2.xtndprce, 0) AS ExtendedPrice
      ,isnull(sop2.ItemCost, iv1.currcost) AS ItemCost
from iv00101 iv1 with (nolock)
left join iv00102 iv2  with (nolock) on iv2.itemnmbr=iv1.itemnmbr
      and iv2.locncode='COMPANY'
left join pm00200 pm2 with (nolock) on pm2.vendorid=iv2.primvndr
left join (
    select itemnmbr, max(unitprce) AS unitprce,
           sum(xtndprce) AS xtndprce, max(unitcost) AS unitcost
    from sop10200_sop30300_union with (nolock)
    group by itemnmbr
) as sop2 on sop2.itemnmbr=iv1.itemnmbr
left join sop10100_sop30200_union sop1 with (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'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.