I need to create a default summed column where the total column equals the SUM of the Amount column.
It is taking into consideration that the user may decide to invest more than once.
So when I so selects in the future I do not have to write the SUM code over and over.
/****** Object: Table [Investors].[InvestorAmount] Script Date: 2/6/2017 4:18:32 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [Investors].[InvestorAmount]( [ID] [int] IDENTITY(121,1) NOT NULL, [InvestorID] [int] NOT NULL, [Amount] [money] NOT NULL, [Total] [money] NOT NULL, [Created] [datetime] NOT NULL, CONSTRAINT [PK_InvestorAmount] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [Investors].[InvestorAmount] ADD CONSTRAINT [DF_InvestorAmount_Created] DEFAULT (getdate()) FOR [Created] GO ALTER TABLE [Investors].[InvestorAmount] WITH CHECK ADD CONSTRAINT [FK_InvestorAmount_InvestorID_ID] FOREIGN KEY([InvestorID]) REFERENCES [Investors].[Investor] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [Investors].[InvestorAmount] CHECK CONSTRAINT [FK_InvestorAmount_InvestorID_ID] GO
Here is what I've come up with so far.
I need the Total amount to be calculated based on the InvestedID the the Amount column to be summed into the Total column.as the Default Value
SELECT SUM(ia1.Amount)FROM Investors.InvestorAmount ia1 JOIN Investors.InvestorAmount ia2 ON ia1.InvestorID = ia2.InvestorID