Jason p
Jason p

Reputation: 121

How do I make a table that is based on the sum of other columns in MySQL?

I need help phrasing this question. I am new to MySQL. I bet this question was asked, but I do not know the terminology to ask the right question.

Let's say I have two tables in MySQL. Table 1 is where new data gets inserted. Table 2 should automatically update. The data within Table 2 is a sum of specified columns within each row of table 1.

For example,

TABLE 1
| ID |  A |  B |  C |  D |
|----+----+----+----+----|
| 0  | 11 | 10 | 39 | 11 |
| 1  | 32 | 21 | 21 | 12 |
| 2  | 12 | 23 | 24 | 13 |
| 3  | 45 | 35 | 31 | 14 |

TABLE 2
| ID | ABCD | AB | CD | 
|----+------+----+----|
| 0  |  71  | 21 | 50 |
| 1  |  86  | 53 | 33 |
| 2  |  72  | 35 | 37 |
| 3  |  125 | 80 | 45 |

Upvotes: 0

Views: 42

Answers (2)

Nick
Nick

Reputation: 147146

An alternative to a VIEW is to add VIRTUAL generated columns to Table1. You can add them using an ALTER TABLE command:

ALTER TABLE Table1
ADD COLUMN ABCD INT GENERATED ALWAYS AS (A+B+C+D) VIRTUAL,
ADD COLUMN AB INT GENERATED ALWAYS AS (A+B) VIRTUAL,
ADD COLUMN CD INT GENERATED ALWAYS AS (C+D) VIRTUAL;

The use of the VIRTUAL keyword means these values are computed when rows are read, rather than being stored in the table. Having done this, you can now

SELECT * FROM Table1

Output

ID  A   B   C   D   ABCD    AB  CD
0   11  10  39  11  71      21  50
1   32  21  21  12  86      53  33
2   12  23  24  13  72      35  37
3   45  35  31  14  125     80  45

Demo on dbfiddle

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

I suggest creating a view here:

CREATE VIEW yourView AS (
    SELECT
        ID,
        A + B + C + D AS ABCD,
        A + B AS AB,
        C + D AS CD
    FROM TABLE_1
)

It is probably not an efficient solution to try to actually maintain these data in an actual materialized table, because the data is derived.

Upvotes: 3

Related Questions