0

This is my Invoice 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,
    [UnitCostColumn]     INT           NOT NULL,
    [DiscountRateColumn] FLOAT (53)    DEFAULT ((0)) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

I want to have a Total Row, TotalQTY, and a Total Cost column. I created the Total Row and TotalQTY with this code (adding a dynamic column) in a stored procedure:

CREATE PROCEDURE GetInvoice
AS
    SELECT 
        ROW_NUMBER() OVER (ORDER BY NameColumn) AS RowNumberColumn,
        CodeColumn,
        NameColumn,
        SUM(QTYColumn) AS TotalQTY,
        UnitCostColumn,
        DiscountRateColumn,
        UnitCostColumn * SUM(QTYColumn) AS TotalRow
    FROM 
        InvoiceTable
    GROUP BY 
        CodeColumn, NameColumn, UnitCostColumn, DiscountRateColumn

My problem: I'm not able to have Total Row's value in the invoice table. Why? Because when I do SELECT * FROM INVOICE TABLE, it shows all columns except TotalRow and TotalQTY. So when I don't have these in the table how can I have the Total Cost column? I was thinking that if I had the total Row column, I would be able to use ExecuteScalar somehow in my C# program to sum up Total Row as Total Column. I would appreciate if someone could tell me how to use ExecuteScalar in this example.

Improve this question
8
  • 1
    You've created a query inside store procedure but you're looking for the column inside the table? Commented Nov 19, 2018 at 18:46
  • @SalmanA yes. please tell me whats wrong :( Commented Nov 19, 2018 at 18:54
  • @absolute455, SalmanA was saying what's wrong; the "dynamic columns" you've created aren't available on your table, so when you query your table you don't see them. Rather, you've created a stored procedure which runs a query; so to see the results of that query, you'd need to execute the stored procedure (exec GetInvoice) and look at the results of that. Commented Nov 19, 2018 at 20:07
  • @JohnLBevan ok fine I got it. But how can I actually have a dynamic column and be able on my table I dont understand this. whats the correct way to do what I do with my store procedure. I want to know these. :( I would appreciate it if you tell me. Commented Nov 21, 2018 at 5:46
  • See the answer by ZekiGumus: stackoverflow.com/a/53380926/361842 on how to get the results in a query, or Estaban if you want these calculated columns to appear on your table stackoverflow.com/a/53381025/361842. What about their suggestions doesn't work for you? Are you not seeing the output you expect, getting an exception, etc? If so, try sharing more of your code as perhaps there's something else that also needs to be amended down the line... Commented Nov 21, 2018 at 7:04

2 Answers 2

Reset to default
2

for each of the total product valuations, you could support yourself with a computed column.

CREATE TABLE [dbo].[InvoiceTable] (
    [Id]                 INT           IDENTITY (1, 1) NOT NULL,
    [CodeColumn]         NVARCHAR (50) NOT NULL,
    [NameColumn]         NVARCHAR (50) NOT NULL,
    [QTYColumn]          INT           NULL,
    [UnitCostColumn]     INT           NOT NULL,
    [DiscountRateColumn] FLOAT (53)    DEFAULT ((0)) NULL,
    [Total w/Discount] AS ([QTYColumn] * [UnitCostColumn] * (1-[DiscountRateColumn])) PERSISTED, /* In case you need data persistence */
    [Total Cost] AS ([QTYColumn] * [UnitCostColumn]) PERSISTED, /* In case you need data persistence */
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

If you already have a table and need to add a computed column you can use the ALTER TABLE command; e.g.

ALTER TABLE [dbo].[InvoiceTable]
ADD [Total Cost] AS ([QTYColumn] * [UnitCostColumn]) PERSISTED

About PERSISTED

The PERSISTED word is optional.

  • If PERSISTED is specified, the value for this field is calculated when the row is inserted or updated; ensuring that the calculated value is calculated once, so each query doesn't incur the cost of calculation. However, this will create a performance penalty for the insert/update, as it will have to be calculated at that time. It will also use more space, since you're now remembering (persisting) the calculated value, so have to store this data somewhere.
  • If PERSISTED is not specified, the value is calculated each time the field is returned from a query, which may have an impact on the query's performance (i.e. time to perform the calculation for each row's computed column(s)), but you'll have less overhead on your insert/update statements, and will require less space.

These are general guidelines; SQL will do things under the covers to try to always give the best performance it can with the information available... more information on that here: https://dba.stackexchange.com/a/136740/10820 (though that's probably going too deep for now).

If in doubt, I'd say go with PERSISTED... in most scenarios you read data more often than you write it, and the amount of additional space to store this information is negligible (plus storage is pretty cheap). Once you've got a bit more experience, you can review that decision and amend if you determine that it's not the best for your specific scenario.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

what is this PERSISTED ? what is data persistance?
okay fine but it doesnt solve my problem I need sum QTYcolumn as totalQTY and totalQTY * unitcostcolumn as TotalRow...
for a data store to be considered persistent, it must write to non-volatile storage. (In-memory)
2

I believe you need something like that:

SELECT *
        ,SUM(QTYColumn) OVER (PARTITION BY Id) TotalQty
        ,SUM(UnitCostColumn*QTYColumn) OVER (PARTITION BY Id) TotalRow
FROM InvoiceTable
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.