How Can I Sum up multiple columns and put the result into another existing column?

user_10451177 0
Viewed 84

This is my table:

   CREATE TABLE [dbo].[InvoiceTable] (
        [Id]                 INT           IDENTITY (1, 1) NOT NULL,
        [CodeColumn]         NVARCHAR (50) NOT NULL,
        [NameColumn]         NVARCHAR (50) NOT NULL,
        [QTYColumn]          INT           NULL,
        [TotalQTYColumn]     INT           NULL,
        [UnitCostColumn]     INT           NOT NULL,
        [TotalCost]          INT           NULL,
        [DiscountRateColumn] FLOAT (53)    DEFAULT ((0)) NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );

I want something like this:

SELECT 
CodeColumn,
NameColumn
TotalQTYColumn = SUM(QTYColumn   
TotalCost = SUM(UnitCostColumn)*SUM(QTYColumn))
blah
FROM InvoiceTable

This doesnt solve my problem:

select
blah
    SUM(UnitCostColumn)*SUM(QTYColumn) AS TotalCost
    SUM(QTYColumn) AS TotalQTYColumn, 
blah

cause I want to add the resualt to an existing column in my table not something new...

this didnt work for me:

SELECT
CodeColumn,
NameColumn,
(select TotalQTYColumn = SUM(QTYColumn))
UnitCostColumn,
DiscountRateColumn,
(select TotalCost = SUM(UnitCostColumn)*SUM(QTYColumn))
FROM InvoiceTable
GROUP BY CodeColumn, NameColumn, UnitCostColumn, DiscountRateColumn

I dont know why it doesnt show the results in TotalCost Column and TotalQTY column when I want to see the results on a datagridview.

tsql sql-server sql
4 Answers

If you want the Sum of the entire column and not a specific result set you mus leave the group by out and make use of a sub query such as 

SELECT (select sum(QTYColumn) FROM InvoiceTable) as 'TotalQTYColumn',NameColumn, CodeColumn FROM InvoiceTable;

that should lead you on the right path

Admin1

You can try below

SELECT
CodeColumn,
NameColumn,
SUM(QTYColumn) as TotalQTYColumn
UnitCostColumn,
DiscountRateColumn,
SUM(UnitCostColumn)*SUM(QTYColumn) as TotalCost
FROM InvoiceTable
GROUP BY CodeColumn, NameColumn, UnitCostColumn, DiscountRateColumn
user_69148640

You are not doing the right thing. You try to UPDATE the value of the column inside a SELECT statement. You should do

update table InvoiceTable (TotalQTYColumn, TotalCost)
values(sum(QTYColumn), sum(UnitCostColumn) * sum(QTYColumn))
where Id = @id

And do this inside a loop where you define the @id. And then you can just make a SELECT statement to retreive the value.

But I think you have an issue in the logic of your table.

Why do you have 2 columns that contains the total quantity of columns in the same table? You have a lot of replication inside your table with thoses 2 columns and more of that you need to update thoses values every single time you insert a new value inside your table...

You should erase [TotalQTYColumn] and [TotalCost] from your table because there are dynamic values and do

    SELECT 
    CodeColumn,
    NameColumn
    SUM(UnitCostColumn)*SUM(QTYColumn) AS TotalCost
    SUM(QTYColumn) AS TotalQTYColumn
    FROM InvoiceTable

You want to add the result of the SUM to an existing column but in your case you are trying to UPDATE the value of the columns in the SELECT statement and that's wrong.

user_84045450

I am looking at your query and thinking that the arithmetic is not what you want. Does this do what you want?

SELECT CodeColumn, NameColumn
       TotalQTYColumn = SUM(QTYColumn)  
       TotalCost = SUM(UnitCostColumn * QTYColumn)
FROM InvoiceTable
user_11440350
Related