How do I create a table the same time as other tables that pulls totals from each?

问题: For class I'm creating a database that keeps track of my finances. I have a table for each type of item I purchase. For example Rent, Food, Hygiene, Entertainment,etc... I...

问题:

For class I'm creating a database that keeps track of my finances. I have a table for each type of item I purchase. For example Rent, Food, Hygiene, Entertainment,etc... I also have a table called Register that I want to display the monthly total for each item. The column names for the totals in the tables are as follows: MonthlyRentTotal, MonthlyFoodTotal, etc... I want the Register table to pull the total from each Table so I don't have to enter them twice. Any Ideas on how I can do that? I don't want to create a view either. I want it to be an actual table. I'm not even sure if this is possible.


回答1:

I assume that Rent, Food, Hygiene, Entertainment have same column type.

INSERT INTO Register
SELECT *
FROM
  (SELECT SUM(a.rent_value) AS value,
          'monthlyrent' AS TYPE
   FROM Rent a
   UNION SELECT SUM(b.food_value) AS value,
                'monthlyfood' AS TYPE
   FROM Food b) d pivot(max(value)
                        FOR TYPE IN (monthlyrent, monthlyfood)) piv;

Data was pivoted in order to be inserted into Register table.

enter image description here

You can put this query in a stored procedure or simply run it manually. If you want to have updated data in the Register table I suggest to :

  • Create a stored procedure using a TRUNCATE for Register table followed by the above query
  • Create an SQL Job and schedule the run of the stored procedure anytime you need.

Hope this helps. Let me know if you need additional details.


回答2:

You should only separate the items into separate tables if there is a compelling reason. For the items you describe, I see no compelling reason.

I would imagine a data structure like this:

  • itemCategories -- contains the list of categories you care about, such as "food", "hygiene", and so on.
  • Purchases -- contains each purchase, with columns like purchaseDate, location, itemCategory, description, and so on.

You may want additional tables for other entities, such as "location".

What you are calling a Register table would then simply be a query or view on Purchases.

  • 发表于 2019-03-20 19:42
  • 阅读 ( 144 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除