Solved

# SQL Syntax update totals

Posted on 2013-06-13
389 Views
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.

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
Question by:rwheeler23
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 3

Assisted Solution

tsnirone earned 125 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
set A.TOTAL=B.SUMA
where A.PONUMBER = B.PONUMBER
go

HTH :)
0

LVL 8

Accepted Solution

didnthaveaname earned 250 total points
ID: 39245771
You can also try:

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

I like this question =)  Hope it helps!

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

Author Comment

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

set SUBTOTAL= (select sum(extdcost) from PODETAIL where ponumber=p2.ponumber)
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

PortletPaul earned 125 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 */
SET TOTAL = isnull(PD.TOTAL, 0) -- changed here, assumes you want zero if no details
LEFT JOIN -- changed here
(
SELECT
PONUMBER
, sum(quantity * unitCost) AS TOTAL
FROM PODETAIL
GROUP BY PONUMBER
) AS PD ON PH.PONUMBER = PD.PONUMBER;
``````
{+ sqlfiddle now up (it was down), added a non-matching poheader (9999) see http://sqlfiddle.com/#!3/dbb12/1 }
0

Author Closing Comment

ID: 39260424
Thanks for the tips
0

## Featured Post

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month5 days, 2 hours left to enroll