SQL Calculated field on details

[P00HB33R]

Hi Guys,

Is there a way that I can sum a field in the details table of a master-detail setup.

So basically I need a field "Line Total" in the master table that sums the "unit price * qty" field of the details table.

Tables are as follows: Invoice -> Invoice Line -> BOM Line

The Invoice can have many Invoice Lines. 1 Invoice Line can have many BOM Lines.

So I need the total of BOM Lines in the invoice line record.

Thanks

 

[lerouc]

select *, (select sum(unitprice * qty) as BOMLineTotal from bomline b where b.invoicelineid = i.invoicelineid) from invoiceline i

something like this?

 

[P00HB33R]

Yeah more or less, but I need the value to be calculated in the Invoice Line "Line Total" field as I add the BOMlines

 

[Nod]

Here is something for MSSQL: https://msdn.microsoft.com/en-us/library/ms188300.aspx

 

[lerouc]

update invoiceline
set bomlinetotal = (select sum(unitprice * qty) from bomline b where b.invoicelineid = i.invoicelineid)
from invoiceline i

fire that off on each bom line insert or if you know when you have added the last line.

or you can add it each time but i would rather recalc after each line if you are not tooo worried about performance.

 

[Kosmik]

Storing it via update does make sense as above or you can write a query with a CTE that sums the BOM lines and join it to the detail of your main query with header and lines. This calculates every run though so may have a knock.

Alternatively you can write a trigger for the update on the BOM.

Manyways to skin this cat, each with pro's and cons.

 

[Oppiekoffie]

Rule of storing data, don't store what you can compute. So build in into the select, what language is the app you returning the data to?

 

[Pho3nix]

MSSQL newer versions allow you to do what you want. Will check when I get home

 

[Oppiekoffie]

MSSQL newer versions allow you to do what you want. Will check when I get home

True. As I'm busy refreshing my SAP ASE a bit thinking about MSSQL grouping , OMG :wtf: :wtf: :wtf:

 

[Hamster]

ROLLUP?

 

[Kosmik]

ROLLUP?

Learn something new everyday.

 

[Oppiekoffie]

ROLLUP?

Question begs, why don't MS take away the thing that you need all columns in group by in select?

 

[Hamster]

Question begs, why don't MS take away the thing that you need all columns in group by in select?

That's not MS, that's the SQL standard. And think about it, how will it know what to display for columns not grouped by unless they're aggregated or similar with MIN, MAX etc. functions?

 

[Oppiekoffie]

That's not MS, that's the SQL standard. And think about it, how will it know what to display for columns not grouped by unless they're aggregated or similar with MIN, MAX etc. functions?

Standard, OK, thought it was a MSSQL thing. Can't remember now how ASE works with it, mgiht be group col, group col, total col1, total col2 etc. If you have group col, group col, not grouped col, total col1, total col2 etc you get an error. But you still don't need to have all columns. Does that answer your question? I think having that ability to not grou[ by all columns in select avoids CTE, rollup and whatever else MS has.

 

[dabean]

http://stackoverflow.com/a/19028172

 

[envo]

While calculated/computed columns can be done, and is okay, I 2nd the "don't store what you can compute" comment.

If you're doing it to see the data in MS SQL Management studio better, then you need to learn how to use saved scripts. It doesn't make sense, at scale, to do that and can have a very negative impact on the speed of the server. You can only throw THAT much hardware at the problem until you realize your DBA skills aren't up to scratch

Seen it happen many times

Now, in a scenario where you're doing a data warehouse, it might make more sense, but even then, I'd save the actual calculated value and not use computed columns

 

[[)roi(]]

"don't store what you can compute" -- assumption of minimal compute cost

 

[DA-LION-619]

"don't store what you can compute" -- assumption of minimal compute cost

A question for dev's here that use an ORM.

Is it better to have the query execute on the SQL server or in memory in the application.

StackOverflow does it in memory.

 

[Hamster]

A question for dev's here that use an ORM.

Is it better to have the query execute on the SQL server or in memory in the application.

StackOverflow does it in memory.

You mean inline SQL vs stored procedures?

 

[DA-LION-619]

You mean inline SQL vs stored procedures?

An example.

Executes on SQL Server
var result = db.Table.Where(x=> x.Subject == subject).ToList();

In memory
var result = db.Table.ToList();
var matches = result.Where(x=> x.Subject == subject).ToList();