Solved

sum some fields in a table with numerical fields

Posted on 2002-03-06
29
225 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

785 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