There's more than one way to do things PerlMonks

### Re: array of arrays calculations

by thezip (Vicar)
 on Jan 19, 2011 at 04:41 UTC ( #883056=note: print w/replies, xml ) Need Help??

in reply to array of arrays calculations

Why not just do the calculation right in the SQL?

```select h.cus,
h.ta,
h.gd1 - c.gd1 as diff1,
h.gd2 - c.gd2 as diff2,
from hist h, hist c
where h.cus = c.cus
and h.ta = c.ta
order by h.cus, h.ta

WARNING: not tested! YMMV!

What can be asserted without proof can be dismissed without proof. - Christopher Hitchens

Replies are listed 'Best First'.
Re^2: array of arrays calculations
by CountZero (Bishop) on Jan 19, 2011 at 07:07 UTC
I didn't try it, but don't you need a "LEFT JOIN" in there somewhere to get the contents of h.cus and h.ta in case c.cus and c.ta don't exist?

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Re^2: array of arrays calculations
by Generoso (Prior) on Jan 19, 2011 at 17:12 UTC
```SELECT h.e_id,
h.cus,
h.gd1-ifnull(t.gd1,0) as dif1,
h.gd2-ifnull(t.gd2,0) as dif2
FROM tabla1 h
left join tabla2 t on h.e_id = t.e_id and h.cus = t.cus;

This works

Re^2: array of arrays calculations
by rocky13 (Acolyte) on Jan 20, 2011 at 02:28 UTC
First, i updated the query in the question little bit to simplify the understanding of the question. As you can see, there are multiple rows for each cus, ta, gd1, gd2 and number of rows in one table is not the same as number of rows in other table.Thus, I think it would be best to group them by cus, ta while getting the sum of gd1 and gd2. I thought maybe there is a way with "union all". I did get the solution by using a hash for each and then doing the computation. However, if there is a way to do it just using sql query, I would love to know. Thanks!

Create A New User
Node Status?
node history
Node Type: note [id://883056]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (5)
As of 2020-01-22 00:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
The worst excuse I have ever heard is:

Results (100 votes). Check out past polls.

Notices?