Geez this thing LOL! posted before I was ready.


Hope some knowledgeable soul can tell me how to do this. I have a table
with 3 cols: an ID number and 2 quantities. I can sum up each of the quantities
easily by checking the ID column and putting "calc sum" in the other 2 cols.
But I also need to add a 3rd column which is the sum total of the other
2 calc sum columns.

In SQL it looks something like this:

select ID_num, sum(q1), sum(q2), (sum(q1) + sum(q2)) as total_sum
from table1
group by ID_num


but how do I do this with a QBE? It's imperative that i do this with QBE
because this will be contained in a script with a whole bunch of other QBEs
and they use tilde variables to work together systematically, and I haven't
had any luck using tilde variables in in SQL queries in .ssl scripts.

I tried this - it provided an answer but it was the wrong one.

ID_num | q1 | q2 |
check | _1, calc sum | _2, calc sum, calc _1 + _2 as total_sum |


Any other suggestions? Thanks!
-Rick M.

select ID_num, sum(q1), sum(q2), (sum(q1) + sum(q2)) as total_sum
from table1
where ID_num = ~sID

or:

where ID_num = '~stID'

....depending on whether it's a string or not and whether your variable
includes the single quotes or not.

As for the QBE, I'm fairly sure you'll need two of them - since they're
in a script, that shouldn't be such a big deal.

Liz


RickM wrote:

Hello Liz, thanks so much for the help! I didn't think about doing it as
2 separate queries - that's a good idea, I'll try that next - thanks!

The tilde vars - yes I know that's what I was doing but I never could get
it to work! But I just went back and played with it some more and now it's
working. I think there was something about how I was opening the database
variable for it - I was doing it wrong.

-Rick

Liz McGuire <liz (AT) paradoxcommunity (DOT) com> wrote: