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.
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 likepurchaseDate
,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
.