Arithmetic Calulation

Question

Post reply

Arithmetic Calulation

mohamed-891698

SSC Enthusiast

Points: 119
More actions
September 27, 2010 at 4:47 am

#241011

I have table tbl_A and tbl_B with following data
seqno amt1 amt2 amt3 total
1 10 10 20 40
2 20 10 20 40
3 30 10 20 40
tbl_B
seqno calculation
4 1+2-3
Now i want to insert a row into tbl_A with seqno 4 and row1+row2-row3(1+2-3). tbl_A is temporary table which creates columns dynamically, so we don't have fixed number of colums in tbl_A.
Please help me.
Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

so we don't have fixed number of colums in tbl_A

Then you really are stuffed. 🙂

You will have to generate a dynamic sql statement to do this , if you dont know the finite column list there is no option. Make me feel dirty just to think about it.

The 'correct' solution is to define tbl_a properly.

so something like.

SeqNo,

AmountCounter,

Total

See this link..

Whenever I see a table with repeating column names appended with numbers, I cringe in horror

http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

Clear Sky SQL
My Blog[/url]

mohamed-891698 SSC Enthusiast Points: 119 More actions · Answer 2

mohamed-891698

SSC Enthusiast

Points: 119

September 27, 2010 at 5:12 am

#1227614

if i have definite number of columns, how should do?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

If you know the number of columns, then you know how many sum() statements to generate. In your example above:

insert into tbl_a

select 4, sum(amt1), sum(amt2), sum(amt3), sum(total)

from tbl_a

However, I have to ask why you are inserting totals rows into tbl_a? This is something that gets done a lot in spreadsheets, but not in database tables. You can always show a grand total in your results by doing a ROLLUP of the totals

select seqno, sum(amt1) as amt1, sum(amt2) as amt2, sum(amt3) as amt3

from tbl_a

group by seqno with rollup

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

mohamed-891698 SSC Enthusiast Points: 119 More actions · Answer 4

mohamed-891698

SSC Enthusiast

Points: 119

September 27, 2010 at 6:17 am

#1227635

1+2-3 has to be inserted

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Because you have multiple rows in your source WHICH 1,2 and 3 are you referring to ?

Clear Sky SQL
My Blog[/url]

mohamed-891698 SSC Enthusiast Points: 119 More actions · Answer 6

it is row1+row2-row3 for each column and insert as new row.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

So , something like this ?

select 4, sum(case when seqno =3 then 0-amt1 else amt1 end) as amt1,

sum(case when seqno =3 then 0-amt2 else amt2 end) as amt2,

sum(case when seqno =3 then 0-amt3 else amt3 end) as amt2

from tbl_a

Clear Sky SQL
My Blog[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Again, this sounds like spreadsheet work. You have no attributes to your define your sets for calculation other than Row #1, Row #2, Row #3. Are you ever going to have more than three rows? If so, what does the formula become then?

Dave's code will work, but I'd really like to understand the *why* behind this requirement.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills