Combining two tables and automatically calculating the total cost-CodePudding

I am trying to find a way to combine two tables and have the total cost of the items in the last column of the second table be automatically calculated when I execute the code.

I can add the items and quantity columns to table1 but I can only add the cost column to table2. I get an error when I want to leave the total column blank.

I assume the total can be automatically added with the right code.

If someone can help me with the code, I would really appreciate it.

What I have currently done:

create table table1(
item varchar(255),
quantity int,
);

create table table2(
cost int,
total int,
);

insert into table1(item, quantity)
values('couch', '1');

insert into table2(cost, total)
values('5', );

table1

item    quantity
TV      1
Couch   2
Bed     1

table2

cost    total
50  
50
50

End result I am looking for

item    quantity    cost    total
TV          1       50      50
Couch       2       50      100
Bed         1       50      50

CodePudding user response：

;with cte as( select item,quantity from table1), cte1 as (select cost from table2 ) select distinct item,quantity,cost, case when quantity=1 then costquantity when quantity=2 then costquantity end as total from cte,cte1

CodePudding user response：

Not sure you would need two separate tables for this. That would require Primary and Foreign keys to work as it should and with your current columns it would be incorrect to draw any relation between the two tables.

That being said, if you used one table you could auto calculate the total col by using a subquery when inserting values.

CREATE table table1(
item varchar(255),
quantity int,
cost int,
total int,
);

INSERT INTO Table1 (item, quantity, cost, total)
SELECT
 S.item [item]
,S.quantity [quantity]
,S.cost [cost]
,(S.quantity * S.cost) [total]
FROM
(
SELECT
    'couch' [item]
    ,5 [quantity]
    ,50 [cost]
) S
GROUP BY S.quantity, S.cost, s.item

SELECT * FROM table1;