Solved

sum some fields in a table with numerical fields

Posted on 2002-03-06
29
227 Views
Last Modified: 2010-04-05
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
Comment
Question by:ugross
  • 11
  • 10
  • 8
29 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6844082
?
select sum(col1+col2), sum(col1), sum(col2) from atable

should result in

10   4    6

select the sum you need

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6844085
maybe you should show the result you want to have
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6844170
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 6844219
yep,
but the group by clause is unusual
because there is no function,
which needs grouping
0
 

Author Comment

by:ugross
ID: 6844757
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
 
LVL 45

Expert Comment

by:aikimark
ID: 6845305
Select colRowID, Sum(col1) As RowSum
From tablename
Group By colRowID
0
 
LVL 45

Accepted Solution

by:
aikimark earned 100 total points
ID: 6845360
or probably more correct:

Select colRowID, Sum(col1+col2) As RowSum
From tablename
Group By colRowID
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6845367
? row
update tablename set sumfieldname = field1+field2+field3...

use the execSQL-method for the update
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6845371
well this was for all rows at once
for a specific row add a whereclause
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6845430
...and this ONLY works if you have row-identifying column(s) upon which to group and, potentially, join with another table.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6846515
? 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
 

Author Comment

by:ugross
ID: 6846521
sorry kretzschmar you r right, wil put in a q with same headline for u, same points, thx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6846676
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
 

Author Comment

by:ugross
ID: 6846683
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6846697
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
 
LVL 45

Expert Comment

by:aikimark
ID: 6846723
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6846735
well,
no offense meant, aikimark,
guessing we both were
near on the answer,
but not exact
meikl ;-)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6846757
no offense taken, meikl

===== haiku ==============
When blind EXperts stand
Near questioner's elephant
Who knows the whole truth?!?
0
 

Author Comment

by:ugross
ID: 6846888
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6846915
try this

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

meikl ;-)
0
 

Author Comment

by:ugross
ID: 6846920
looks good but
error now is:
expected 'END' but ')' was found
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6846923
a bracket too much, sorry

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

0
 

Author Comment

by:ugross
ID: 6846936
great stuff u deserved the points
0
 

Author Comment

by:ugross
ID: 6847064
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
 
LVL 45

Expert Comment

by:aikimark
ID: 6847373
edit1.text := intToStr(StrToInt(edit1.text)/20);
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6847385
better yet, do the division when you sum to comboboxes:

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

Author Comment

by:ugross
ID: 6847394
thx when trying this im getting an error telling me thjat theres no overloaded version of inttostr
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6847408
use floattostr (or formatfloat()) instead of the first inttostr,
because a extended is returned
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6847418
Beware of floating-point arithmetic operation results as well as the Ides of March. :-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question