Solved

sum some fields in a table with numerical fields

Posted on 2002-03-06
29
220 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now