?
Solved

sum some fields in a table with numerical fields

Posted on 2002-03-06
29
Medium Priority
?
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 46

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 46

Expert Comment

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

Accepted Solution

by:
aikimark earned 400 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 46

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 46

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 46

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 46

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 46

Expert Comment

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

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 46

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

752 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