Adding Computed values to columns

Transact-SQL
1

HI,

I have columns Name, lower limit , upper limit, A_Value and N_Value.
I have created a new column RESULT1 which is the diff between A value and N value
ALTER TABLE table name ADD RESULT1 AS (A_Value - N_Value );

I created a new Column called "In/Out" which is Case statement
CASE when ([A_Value]-[N_Value])>=[lower limit] AND ([A_Value]-[N_Value])<=[lower limit] then (1) else (0) end)

Now I need to create a third column called "percentage" which will give me the percentage of values inside the limit

the formula is (100*SUM(InOut)/COUNT(InOut)) and I have to group it by Name.

I created a new column called percentage float()
I am using the statement
Update Table name set percentage = (100*SUM(InOut)/COUNT(InOut)) Group by Name.
and it is throwing an error. I want to know how I can add the computed values to the column percentage.

Thanks in advance!

2 
UPDATE a SET
	Percentage = b.Result
FROM
	Tbl a
	CROSS APPLY
	(
		SELECT 100.0 * SUM(t2.Inout)/COUNT(t2.InOut) AS Result
		FROM Tbl b
		WHERE a.Name = b.Name 
	) b
3

Hi James,

Thanks for the reply.

I just have one table but in the above query you seem to be using two tables Tbl a and Tbl b.

4

Replace Tbl with your table name in both places, so it will be:

UPDATE a SET
	Percentage = b.Result
FROM
	YourTableNameHere AS a
	CROSS APPLY
	(
		SELECT 100.0 * SUM(t2.Inout)/COUNT(t2.InOut) AS Result
		FROM YourTableNameHere AS b
		WHERE a.Name = b.Name 
	) b
1 Like
5

Hi James,

Thanks a ton. It worked :smile:

6

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

have columns vague_generic_name, something_lower_limit, something_upper_limit, a_value and n_value. <<

But because you have no manners, we have no such information! “name” of what? Upper and lower limits of what? I will assume that a_value and n_value are standard terms in your trade, so they make sense.

I have created a new column result1 which is the difference between a_value and n_value <<

Why? This just wastes time and space. Do it as a computation in a VIEW or query.

ALTER TABLE Foobar ADD result1 AS (a_value - n_value);

I created a new Column called "In/Out" [sic] which is CASE statement [sic: this is an expression, not a statement]. Did you really use a slash in a data element name?

(CASE WHEN (a_value - n_value) >= lower_something_limit
AND (a_value – n_value]) <= lower_something_limit
THEN 1 ELSE 0 END) AS silly_flag

Please remember 5-th grade math. Reduce the inequalities:

(a_value – n_value) = lower_something_limit;

Did you mean:

(CASE WHEN (a_value - n_value)
BETWEEN lower_something_limit AND upper_something_limit
THEN 1 ELSE 0 END) AS silly_flag

This is still bad SQL, but at least the math is right.

Now I need to create a third column called "percentage" which will give me the something_percentage of values inside the limit <<

Not in SQL! We are happy with virtual, computed columns. No more tapes and punch cards in RDBMS.

Since you were too rude to post DDL, you might have done INTEGER math. Is this the right formula? (100.00* SUM(a_n_difference)/ COUNT(a_n_difference)) and I have to group it by vague_generic_name.

I created a new column called something_percentage FLOAT() <<

Where is the code for this? Why did you use FLOAT?

I am using the statement

UPDATE Table vague_generic_name
SET something_percentage = (100*SUM(a_n_difference)/COUNT(a_n_difference))
GROUP BY vague_generic_name;
<<

NO! Think about how a GROUP BY works. I want you to edit this skeleton for data types, keys and give us valid ISO-11179 data element names.

CREATE TABLE Foobar
(vague_generic_name CHAR(10) NOT NULL,
PRIMARY KEY (??),
something_lower_limit ?? NOT NULL,
something_upper_limit ?? NOT NULL,
CHECK (something_lower_limit <= something_upper_limit),
a_value ?? NOT NULL
n_value ?? NOT NULL);

Then give us some sample data and expected results. This is easy to put into a VIEW and not keep doing punch card programs in SQL. But you have to follow minimal Netiquette if you really want help.

7

Do you know that the old Sybase UPDATE..FROM .. does not work? Google it!

8

I have not the pleasure of understanding you. Of what are you talking?

If you are saying that UPDATE..FROM does not work in Sybase: I am not writing the query for Sybase.

If you are saying that UPDATE..FROM is not ANSI SQL: I am not writing ANSI SQL. I am writing T-SQL.

Nonetheless, I took your advice and googled. This is the first result I got, which is Microsoft's official documentation for UPDATE.

If you will, on that page, scroll down to the section "Updating Data Based on Data From Other Tables" where it documents the UPDATE..FROM construct with its behavior and limitations.

So I submit that you are completely wrong in your assertion that the query I posted does not work.

While it is useful and you are welcome to point out errors/omissions/deficiencies in someone's responses, may I respectfully suggest that saying "It doesn't work. Google it" does not add any value to the discussion? I wish you would refrain from doing so, especially so when you are wrong about the assertion.