Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Syntax update totals

Posted on 2013-06-13
5
Medium Priority
?
397 Views
Last Modified: 2013-06-19
I have a situation where I need to update header records based on values in detail records.
In this case there are  PO header and detail tables.
POHEADER.PONUMBER
POHEADER.TOTAL

The detail table has
PODETAIL.PONUMBER
PODETAIL.POLINE
PODETAIL.QUANTITY
PODETAIL.UNITCOST

I need to do a mass update of the POHEADER.TOTAL field based on the sum of the details lines on the corrresponding PO.  So if PO number 1000 has two lines with quantities of 2 and 4 and costs of $50 and $100, respectively. The total cost based on the detail is 2 x 50 and 4 x 100 for a total of $100 + $400 = $500. I need POHEADER.TOTAL to equal $500
0
Comment
Question by:rwheeler23
5 Comments
 
LVL 3

Assisted Solution

by:tsnirone
tsnirone earned 500 total points
ID: 39245392
Been quite a few years since I've dwelved into sql but I think this should work:

select PONUMBER, sum(UNITCOST)*QUANTITY SUMA
from PODETAIL
into #tmp1
group by PONUMBER
go
update POHEADER
from POHEADER A, #tmp1 B
set A.TOTAL=B.SUMA
where A.PONUMBER = B.PONUMBER
go

HTH :)
0
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 1000 total points
ID: 39245771
You can also try:

update POHEADER
set TOTAL = PD.TOTAL 
from
	POHEADER as PH
	inner join
	(
		select
			PONUMBER,
			sum( quantity * unitCost ) as TOTAL
		from
			PODETAIL
		group by
		  PONUMBER
	) as PD on PH.PONUMBER = PD.PONUMBER;

Open in new window


I like this question =)  Hope it helps!

If anyone wants to play with this: http://sqlfiddle.com/#!3/d5f8d/1/0
0
 

Author Comment

by:rwheeler23
ID: 39245941
That SQL Fiddle thing is cool. I did find that this works too! Thanks for the tips.

update POHEADER
set SUBTOTAL= (select sum(extdcost) from PODETAIL where ponumber=p2.ponumber)
from POHEADER p2
where (select sum(extdcost) from PODETAIL where ponumber=p2.ponumber)  is not null

Apparently there were some PO Header line that had no matching PO Detail lines hence the check for NULL.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39246498
from a query efficiency standpoint, doing one summing subquery as proposed by didnthaveaname, would be my recommendation. This tweak is another way to deal with the nulls:
/* zero if no details found */
UPDATE POHEADER
SET TOTAL = isnull(PD.TOTAL, 0) -- changed here, assumes you want zero if no details
FROM POHEADER AS PH
LEFT JOIN -- changed here
          (
          SELECT
               PONUMBER
             , sum(quantity * unitCost) AS TOTAL
          FROM PODETAIL
          GROUP BY PONUMBER
          ) AS PD ON PH.PONUMBER = PD.PONUMBER; 

Open in new window

{+ sqlfiddle now up (it was down), added a non-matching poheader (9999) see http://sqlfiddle.com/#!3/dbb12/1 }
0
 

Author Closing Comment

by:rwheeler23
ID: 39260424
Thanks for the tips
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question