?
Solved

SQL Syntax update totals

Posted on 2013-06-13
5
Medium Priority
?
392 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
[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
  • Learn & ask questions
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

718 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