Here are 2 tables. 1st user data 2nd about all user purchases. Can you please tell me how to make sure that in the first table a certain column shows the sum of all purchases from the second table of a user by his ID? Or even how to make that was the sum of all lines from. tables. Sorry for such questions, learning SQL not so long ago. Tested

CREATE TABLE IF NOT EXISTS asd (n INT DEFAULT (SELECT COUNT(id) FROM users));

but it does not work

  • Do you want the cell in the first table to automatically update the cell with the sum of all purchases when inserted into the second row? - Viktorov
  • @Viktorov rather want a calculated column. - teran
  • @Viktorov Yes, exactly so that the first table automatically updates the cell with the sum of all purchases when inserting it into the second table. - Windows Eight
  • Read about triggers, I don’t know any other way to solve your problem. - Viktorov
  • @Viktorov Thank you, I hope my task will be solved there. Just coming to this section. - Windows Eight

3 answers 3

Here is a sample trigger for solving your problem for MS SQL:

 IF OBJECT_ID('TRG_InsertUpdSales') IS NOT NULL DROP TRIGGER TRG_InsertUpdSales GO CREATE TRIGGER TRG_InsertUpdSales ON dbo.Table2 AFTER INSERT AS BEGIN UPDATE Table1 set sales=sum(column1) from Table2 where Table1.user=Table2.user SELECT * FROM INSERTED END GO
  • Why from all the above question tags, did you choose sqlserver which is not there? :) - teran
  • @teran I suggested that the syntax for creating triggers in hospital cases is about the same, but I was insured by pointing out a “platform” solution) - Nick Proskuryakov
  • Triggers for updating and deleting are also necessary - no one has canceled the return of goods. As well as input errors. - Akina
  • @NickProskuryakov Sorry for such a late reply, but you can do it like this on sqlite - Windows Eight

The idea is this:

 SELECT user.name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = user.id) as ordersCount FROM user

    Create a view; this is better than using a trigger every time after an insert into the second table.

    • On a large scale, this approach is tantamount to a shot in the foot. The trigger will recalculate only 1 time, and for the presentation will have to recount again and again - Viktorov
    • Well, I will consider. Answered from those considerations that if the inserts in the second table are actively going, this leads to a trigger call + update in the first table. And using the view, we will only calculate when you need to get the result. As a rule, I have always seen such an approach. Perhaps it is easier to create a variable that will increment the number of lines and output this variable in the select. - faksel