SQLTeam.com | Weblogs | Forums

How to Sum a Default Column

sql2014

#1

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

#2

@alumcloud,
Try:

DROP VIEW IF EXISTS uvwSumByInvestorID;
GO
CREATE VIEW uvwSumByInvestorID
AS
SELECT SUM(ia.Amount) SUM_Amount, InvestorID
FROM Investors.InvestorAmount ia
GROUP BY InvestorID;
GO