How do I populate one field in Base table by multiplying one field by another

[Sparksvonrou]

I am very new to this. I have created a database for keeping track of items sold to people at an auction. It has the date and an Id number as well as other needed things such as quantity and price and total price.

I want the table to multiply one field which is the quantity purchased by the price and put that in a third field which will be the total. Then I can use query and report to show the items purchased, the cost and description, the quantity, and the total due.

Also each person may have more than one entry in the table if they purchase numerous items since each item is entered when won. So my query as well as my final report must show the total from all the times but the individual item entry in the table must only show the total from that individual entry. I am not sure how to make the query and report show the total from all the items they won in the table.

[doug]

For the query, have you tried subqueries i.e., SELECTa.winner, a.total, b.item FROM (SELECT winner, SUM(amounts) AS total FROM t1 GROUP BY winner) AS a LEFT JOIN (SELECT winner, item FROM t1) AS b ON (a.winner = b.winner) ORDER BY winner . For the report, tryView-->Sorting and Grouping` I have not tested these suggestions.

[Xoristzatziki1]

Although this is an SQL related question (so not in the right place…):

Poking to database calculated values based on other fields in the same record is considered bad practice. You can calculate the value in a query.

The query should be: SELECT winner, quantity, priceofoneitem, (quantity * priceofoneitem) AS total FROM tablewithwinners ORDER by winner

The report will be responsible to group winners and display their sub totals.

[Ratslinger]

Hello,

You can easily do this when using Firebird 3 (and embedded). It has ability to provide Computed Fields. Sample table creation (from Firebird 3 server example):

CREATE TABLE SALARY_HISTORY
(
  EMP_NO EMPNO NOT NULL,
  CHANGE_DATE timestamp DEFAULT 'NOW' NOT NULL,
  UPDATER_ID varchar(20) NOT NULL,
  OLD_SALARY SALARY NOT NULL,
  PERCENT_CHANGE double precision DEFAULT 0 NOT NULL,
  CONSTRAINT INTEG_55 PRIMARY KEY (EMP_NO,CHANGE_DATE,UPDATER_ID)
);

ALTER TABLE SALARY_HISTORY ADD NEW_SALARY COMPUTED BY (old_salary + old_salary * percent_change / 100);
ALTER TABLE SALARY_HISTORY ADD CONSTRAINT INTEG_56
  FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE SALARY_HISTORY ADD CONSTRAINT INTEG_54
  CHECK (percent_change between -50 and 50);
CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY (CHANGE_DATE);
CREATE INDEX UPDATERX ON SALARY_HISTORY (UPDATER_ID);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON SALARY_HISTORY TO  "PUBLIC" WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON SALARY_HISTORY TO  SYSDBA WITH GRANT OPTION;

The entire DB sample was ported from Firebird 3 server and the resulting table works in Firebird embedded (LO v6.0.4 used).

You can also cumulate info with SQL: