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