CODEWITHSUNDEEP
sqldelphisum
user1019835
user1019835

Reputation: 63

Sum a column of fields from an ado-query result?

Ok so I am creating a program that makes use of a dbgrid and ado-queries to work with tables from an Microsoft access database.

The program is designed for a business with a loyalty program, members can be registered, and purchases must be tracked. This is the table with transactions:

enter image description here

Now what I want to do is run a ado query that will select a certain member ID from that table, and then I want to add all the "PointsAwarded" from that result of the adoquery. So I can get the total amount of points one member has earned.

It seems that this is very hard to accomplish as I have been searching for days and haven't found anything useful.

Thanx for any help!

Upvotes: 2

Views: 3645

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53860

Use Golez's answer if you want to get the total PointsAwarded for all of the members.

If you want the PointAwarded for a specific member, then you can simply do this:

SELECT SUM(PointsAwarded) AS TotalPointsAwarded
FROM Transactions
WHERE MemberID = 3

The above example gets you the total for member 3.

You'll want to parameterize the query so that you can pass in your variable for the Member ID.

You'll want to read the docs on the Parameters property for your component since it differs between Delphi versions.

However, once you've parameterized the query, then when you call the query, you do this, where MyADOQuery is the name of your component:

MyADOQuery.Active := False;
MyADOQuery.Parameters.ParamByName('MemberID').Value := 3;
MyADOQuery.Active := True;

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116120

It's not that hard, if you know what to look for. :)

You can group by certain columns, and then use aggregate functions on other columns. In this case, I group all records for each MemberId together to get the sum of awarded points:

select
  t.MemberId,
  sum(t.PointsAwarded) as TotalPoints
from
  Transactions t
group by
  t.MemberId

Upvotes: 2

Related Questions