locked
How to make a calculation (4 columns) and show result in (TotalC) as in the following example ? RRS feed

  • Question

  • I want calculation : C1 - ( C2 * C3 + C4 )  = TotalC

    CREATE TABLE [dbo].[Calculation](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[names] [varchar](50) NULL,
    	[C1] [int] NULL,
    	[C2] [int] NULL,
    	[C3] [int] NULL,
    	[C4] [int] NULL,
    	[TotalC] [int] NULL,
     CONSTRAINT [PK_Calculation] 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
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[Calculation] ON 
    
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (1, N'jon', 1000, 2, 33, 100, NULL)
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (2, N'asama', 1000, 3, 44, 100, NULL)
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (3, N'aziz', 1000, 5, 33, 100, NULL)
    SET IDENTITY_INSERT [dbo].[Calculation] OFF
    

    I want result :

    Monday, October 23, 2017 7:06 PM

Answers

  • update [dbo].[Calculation] set [TotalC]=[C1]-([C2]*[C3]+[C4])  

    This ? 

    I doubt its really that simple you are looking for ? 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Naomi N Monday, October 23, 2017 7:24 PM
    • Marked as answer by srajmuneer Monday, October 23, 2017 8:42 PM
    Monday, October 23, 2017 7:14 PM
  • just do a simple update

    UPDATE dbo.Calculation
    SET TotalC = [C1] - (([C2] * [C3]) + [C4])


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Naomi N Monday, October 23, 2017 7:24 PM
    • Marked as answer by srajmuneer Monday, October 23, 2017 8:42 PM
    Monday, October 23, 2017 7:16 PM
  • One thing I would like to mention is that since your table definition allows NULL value for any column of [C1] to [C4], you may get NULL as TotalC:

    DECLARE @Calculation TABLE (
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[names] [varchar](50) NULL,
    	[C1] [int] NULL,
    	[C2] [int] NULL,
    	[C3] [int] NULL,
    	[C4] [int] NULL,
    	[TotalC] [int] NULL
    );
    
    INSERT @Calculation ([names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (N'jon', 1000, 2, 33, 100, NULL);
    INSERT @Calculation ([names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (N'asama', 1000, 3, 44, 100, NULL);
    INSERT @Calculation ([names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (N'aziz', 1000, 5, 33, 100, NULL);
    INSERT @Calculation ([names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (N'test', 1000, 5, 33, NULL, NULL);
    
    SELECT [names], [C1], [C2], [C3], [C4],  [C1] - ([C2] * [C3] + [C4]) AS [TotalC] FROM @Calculation


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by srajmuneer Monday, October 23, 2017 8:42 PM
    Monday, October 23, 2017 7:32 PM
  • One more comment, if TotalC must ALWAYS contain the sum of C1, C2, C3, C4, then just make it a computed column.  Then you don't need (and indeed cannot) update or insert values into TotalC.  It will just always contain the sum.  For example,

    CREATE TABLE [dbo].[Calculation](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[names] [varchar](50) NULL,
    	[C1] [int] NULL,
    	[C2] [int] NULL,
    	[C3] [int] NULL,
    	[C4] [int] NULL,
    	[TotalC] AS [c1] + [c2] + [c3] + [c4]
     CONSTRAINT [PK_Calculation] 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
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[Calculation] ON 
    
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4]) VALUES (1, N'jon', 1000, 2, 33, 100)
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4]) VALUES (2, N'asama', 1000, 3, 44, 100)
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4]) VALUES (3, N'aziz', 1000, 5, 3, 100)
    SET IDENTITY_INSERT [dbo].[Calculation] OFF
    
    SELECT * FROM dbo.Calculation;

    Tom

    • Marked as answer by srajmuneer Tuesday, October 24, 2017 6:15 PM
    Monday, October 23, 2017 9:58 PM

All replies

  • update [dbo].[Calculation] set [TotalC]=[C1]-([C2]*[C3]+[C4])  

    This ? 

    I doubt its really that simple you are looking for ? 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Naomi N Monday, October 23, 2017 7:24 PM
    • Marked as answer by srajmuneer Monday, October 23, 2017 8:42 PM
    Monday, October 23, 2017 7:14 PM
  • just do a simple update

    UPDATE dbo.Calculation
    SET TotalC = [C1] - (([C2] * [C3]) + [C4])


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Naomi N Monday, October 23, 2017 7:24 PM
    • Marked as answer by srajmuneer Monday, October 23, 2017 8:42 PM
    Monday, October 23, 2017 7:16 PM
  • One thing I would like to mention is that since your table definition allows NULL value for any column of [C1] to [C4], you may get NULL as TotalC:

    DECLARE @Calculation TABLE (
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[names] [varchar](50) NULL,
    	[C1] [int] NULL,
    	[C2] [int] NULL,
    	[C3] [int] NULL,
    	[C4] [int] NULL,
    	[TotalC] [int] NULL
    );
    
    INSERT @Calculation ([names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (N'jon', 1000, 2, 33, 100, NULL);
    INSERT @Calculation ([names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (N'asama', 1000, 3, 44, 100, NULL);
    INSERT @Calculation ([names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (N'aziz', 1000, 5, 33, 100, NULL);
    INSERT @Calculation ([names], [C1], [C2], [C3], [C4], [TotalC]) VALUES (N'test', 1000, 5, 33, NULL, NULL);
    
    SELECT [names], [C1], [C2], [C3], [C4],  [C1] - ([C2] * [C3] + [C4]) AS [TotalC] FROM @Calculation


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by srajmuneer Monday, October 23, 2017 8:42 PM
    Monday, October 23, 2017 7:32 PM
  • One more comment, if TotalC must ALWAYS contain the sum of C1, C2, C3, C4, then just make it a computed column.  Then you don't need (and indeed cannot) update or insert values into TotalC.  It will just always contain the sum.  For example,

    CREATE TABLE [dbo].[Calculation](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[names] [varchar](50) NULL,
    	[C1] [int] NULL,
    	[C2] [int] NULL,
    	[C3] [int] NULL,
    	[C4] [int] NULL,
    	[TotalC] AS [c1] + [c2] + [c3] + [c4]
     CONSTRAINT [PK_Calculation] 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
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[Calculation] ON 
    
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4]) VALUES (1, N'jon', 1000, 2, 33, 100)
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4]) VALUES (2, N'asama', 1000, 3, 44, 100)
    INSERT [dbo].[Calculation] ([ID], [names], [C1], [C2], [C3], [C4]) VALUES (3, N'aziz', 1000, 5, 3, 100)
    SET IDENTITY_INSERT [dbo].[Calculation] OFF
    
    SELECT * FROM dbo.Calculation;

    Tom

    • Marked as answer by srajmuneer Tuesday, October 24, 2017 6:15 PM
    Monday, October 23, 2017 9:58 PM
  • thank you very much Tom Cooper for help , I was really an expert
    Tuesday, October 24, 2017 6:33 PM