Solved

Set Computed Column Specification Or field in Select as sum of child records

Posted on 2007-12-01
3
1,928 Views
Last Modified: 2010-04-21
I want to set a column in my invoice table to be a sum of the values in my invoice items table using computed column specification.

Example: 3 invoice items with a value of 10 in the total field would make the total field in the invoice table = 30 where the invoice.invoiceid value = the invoice items. invoiceid

If that's not possible how would I do it in my select from the invoice table so that my select returns one row for each invoice but has a column that has a sum of all related invoiceitem.total fields for that invoiceid? Something like:
Select SUM(invoiceitem.total) as total, invoiceid from invoices
0
Comment
Question by:advlgx
[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
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20388390
>>I want to set a column in my invoice table to be a sum of the values in my invoice items table using computed column specification. <<
No that is not possible.  You could do it with a Select statement and a Group clause.  But without knowing the table structure it is difficult to be precise.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 20388396
Here is another option using a subquery:

Select Col1, Col2, Col3,
           (Select SUM(Total) From invoiceitem Where invoiceid = i.invoiceid)
From   invoice i
0
 

Author Closing Comment

by:advlgx
ID: 31412099
Thanks!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

710 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