Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

sum some fields in a table with numerical fields

hi there,
i have a smal prob with a sum function. my database is small and consists only of 1 table managed by the bde. in this table r several numerical values. i now want to sum up these numerical rows. how can i do this with the tquery component.thx a lot
     column1   column2
row1    1          2
row2    3          4
0
ugross
Asked:
ugross
  • 11
  • 10
  • 8
1 Solution
 
kretzschmarCommented:
?
select sum(col1+col2), sum(col1), sum(col2) from atable

should result in

10   4    6

select the sum you need

meikl ;-)
0
 
kretzschmarCommented:
maybe you should show the result you want to have
0
 
aikimarkCommented:
to sum the rows you will need another column where you listed row1, row2, etc.  I'll refer to that as colRowID in my example:

Select colRowID, (col1 + col2) As RowSum
From tablename
Group By colRowID, RowSum
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
kretzschmarCommented:
yep,
but the group by clause is unusual
because there is no function,
which needs grouping
0
 
ugrossAuthor Commented:
thx, so far i want to sum up specific fields in one row, lets say row1, then i want to post the result to a field (numeric)in the table, hope its clearer now, thx
0
 
aikimarkCommented:
Select colRowID, Sum(col1) As RowSum
From tablename
Group By colRowID
0
 
aikimarkCommented:
or probably more correct:

Select colRowID, Sum(col1+col2) As RowSum
From tablename
Group By colRowID
0
 
kretzschmarCommented:
? row
update tablename set sumfieldname = field1+field2+field3...

use the execSQL-method for the update
0
 
kretzschmarCommented:
well this was for all rows at once
for a specific row add a whereclause
0
 
aikimarkCommented:
...and this ONLY works if you have row-identifying column(s) upon which to group and, potentially, join with another table.
0
 
kretzschmarCommented:
? i do not agree with the acceptment,
because i already mentioned already a sum(col1+col2)
and a groupby-clause is unusual by a single row select,
its just the same as

Select colRowID, col1+col2 As RowSum
From tablename

meikl ;-)

0
 
ugrossAuthor Commented:
sorry kretzschmar you r right, wil put in a q with same headline for u, same points, thx
0
 
aikimarkCommented:
kretzschmar,

While your 03/06/2002 05:45AM answer of "Sum(col1+col2), Sum(col1), Sum(col2)" did predate my answer, it produced the sum of columns, not a sum-by-row that ugross was seeking.

I don't agree that you deserve points for this, unless ugross really wanted a columnar sum.

From my earliest comment, I was mentioning a rowID column requirement.  It is true that a Group By is superfluous for a primary-key rowID, but is quite useful when rowID might not be unique.

====================================
I don't want to hold you back from getting points, but future readers of this question might think that your "Sum(col1+col2)" answer is equivalent to my answer and solves the ugross problem.
0
 
ugrossAuthor Commented:
hi,
well just to let u know, what im doing at th emoment is an alternative, im not using the query i simply take something like this:
edit1.text := (dbcombobox3.text + dbcombobox4.text);
..tis is to get a sum of the values in the two comboboxes, it gets me a combination of the values but no sum, but this will be only a minor issue (i hope)
0
 
kretzschmarCommented:
to aikimark,

>From my earliest comment, I was mentioning a rowID column
>requirement.  It is true that a Group By is
>superfluous for a primary-key rowID, but is quite useful
>when rowID might not be unique.

in this case it would not be a sum-by-row, means the answer would be incorrect. correct in this case is

Select colRowID, col1+col2 As RowSum From tablename where ...

means your answer is wrong,
if you mean that your answer is for a sum-by-row

regards

to ugross,
if you want the sum of all values in the comboboxes,
which i guess that is your col1 and col2, then
use
select sum(col1+col2) from atable

but i'm at the moment a bit
confused for what you're after,
because u could use a calculted field for this also

meikl ;-)


0
 
aikimarkCommented:
ugross,

edit1.text := (StrToInt(dbcombobox3.text) + StrToInt(dbcombobox4.text));

=========================
kretzschmar,

At the time I gave my suggestion, I/we really didn't know what ugross actually wanted.  One interpretation of "row sum" is "sum by row ID".
0
 
kretzschmarCommented:
well,
no offense meant, aikimark,
guessing we both were
near on the answer,
but not exact
meikl ;-)
0
 
aikimarkCommented:
no offense taken, meikl

===== haiku ==============
When blind EXperts stand
Near questioner's elephant
Who knows the whole truth?!?
0
 
ugrossAuthor Commented:
sorry for this but when im doing
edit1.text := (StrToInt(dbcombobox3.text) + StrToInt(dbcombobox4.text));
im getting an error msg that string and integer r incompatible ..thx

0
 
kretzschmarCommented:
try this

edit1.text := intToStr(StrToInt(dbcombobox3.text) + StrToInt(dbcombobox4.text)));

meikl ;-)
0
 
ugrossAuthor Commented:
looks good but
error now is:
expected 'END' but ')' was found
0
 
kretzschmarCommented:
a bracket too much, sorry

edit1.text := intToStr(StrToInt(dbcombobox3.text) + StrToInt(dbcombobox4.text));

0
 
ugrossAuthor Commented:
great stuff u deserved the points
0
 
ugrossAuthor Commented:
short one:
when i now want to divide the result appearing in the editcontrol by 20 (which is constant), h0ow can i do this in code, thx a lot
0
 
aikimarkCommented:
edit1.text := intToStr(StrToInt(edit1.text)/20);
0
 
aikimarkCommented:
better yet, do the division when you sum to comboboxes:

edit1.text := intToStr((StrToInt(dbcombobox3.text) + StrToInt(dbcombobox4.text))/20);
0
 
ugrossAuthor Commented:
thx when trying this im getting an error telling me thjat theres no overloaded version of inttostr
0
 
kretzschmarCommented:
use floattostr (or formatfloat()) instead of the first inttostr,
because a extended is returned
0
 
aikimarkCommented:
Beware of floating-point arithmetic operation results as well as the Ides of March. :-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now