Solved

sum some fields in a table with numerical fields

Posted on 2002-03-06
29
224 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy file in dll not working but working on exe ! 18 88
code issue 8 100
oracle global variables 4 63
can't find the executable in Simulator 1 80
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

22 Experts available now in Live!

Get 1:1 Help Now