December 14, 2011 at 9:35 pm
Hi,
I want to multiply two columns in SQL nd store can anybody help me out in this scenario.
Example:-
idproduct priceqtytotal
112 13 3(in this column value gets automatically cal) and store
December 15, 2011 at 1:01 am
This was removed by the editor as SPAM
December 15, 2011 at 7:58 am
Hi,
Are you just looking to output the values??
CREATE TABLE #product (
ID INTEGER IDENTITY,
Product INTEGER NOT NULL,
Price MONEY NOT NULL,
Quantity INTEGER NOT NULL
)
INSERT INTO #product
SELECT 1,1.75,10
UNION ALL
SELECT 1,1.75,15
UNION ALL
SELECT 1,1.75,20
UNION ALL
SELECT 2,2.00,8
UNION ALL
SELECT 2,2.00,12
SELECTID,
Product,
Price,
Quantity,
Price * Quantity AS Value,
(Price * Quantity) * .2 AS Tax
FROM #product
December 15, 2011 at 8:04 am
Here is a very good example for computed that you want.
May 30, 2012 at 4:33 pm
Thanks.
This link is really useful...
May 30, 2012 at 6:01 pm
Stewart "Arturius" Campbell (12/15/2011)
if there is need (e.g. the column to be indexed), you could even PERSIST said calculated column
Precise and deterministic computed columns can be indexed without marking them PERSISTED:
CREATE TABLE #Test
(
product_id integer NOT NULL PRIMARY KEY,
pricesmallmoney NOT NULL,
qty smallint NOT NULL,
total AS price * qty
);
INSERT #Test
(product_id, price, qty)
VALUES
(12, $13.00, 3);
SELECT * FROM #Test AS t
CREATE INDEX nc1 ON #Test (total);
DROP TABLE #Test;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 30, 2012 at 11:22 pm
You can use a CTE to update your table as follows:
--Creating Table
Create Table Ex1
(idint,
product int,
price int,
qty int,
total int)
--Inserting Sample Data
Insert Into Ex1(id, product, price, qty) Values(1, 12, 13, 3)
--Query For Your Requirement
;With CTE
As
(Select (price * qty) As Total From Ex1)
Update Ex1
Set total = (Select Total From CTE)
May 31, 2012 at 2:00 am
vinu512 (5/30/2012)
You can use a CTE to update your table as follows:
No! Try that with this data:
Insert Into Ex1(id, product, price, qty) Values(1, 12, 13, 3)
Insert Into Ex1(id, product, price, qty) Values(2, 24, 26, 6)
Msg 512, Level 16, State 1, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 31, 2012 at 4:49 am
SQL Kiwi (5/31/2012)
vinu512 (5/30/2012)
You can use a CTE to update your table as follows:No! Try that with this data:
Insert Into Ex1(id, product, price, qty) Values(1, 12, 13, 3)
Insert Into Ex1(id, product, price, qty) Values(2, 24, 26, 6)
Msg 512, Level 16, State 1, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Yes, there needs to be a Where clause for both Selecting and Updating.
Thanks for bringing it to our notice Paul.
May 31, 2012 at 4:56 am
Use computed column and set peristed in ON
May 31, 2012 at 5:17 am
Sony Francis @EY (5/31/2012)
Use computed column and set peristed in ON
Yes, I agree with Sony. If you want something like an update on the whole table then its better using Computed Columns With Persisted as follows:
--Creating Tables
Create Table Ex1
(idint,
product int,
price int,
qty int,
total As (Price * qty) Persisted) --Computed Column
--Inserting sample Data
Insert Into Ex1(id, product, price, qty) Values(1, 12, 13, 3)
Insert Into Ex1(id, product, price, qty) Values(2, 24, 26, 6)
--Selecting Data
Select * From Ex1
May 31, 2012 at 5:26 am
Why persist it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2012 at 10:34 pm
GilaMonster (5/31/2012)
Why persist it?
It is not necessary to use the Persisted Property according to the OP's original requirements.
I added it to my code because of the following property of using Persistence:
- Any update in referenced column will be synchronized automatically in computed column if it is
Persisted.
June 1, 2012 at 12:40 am
vinu512 (5/31/2012)
GilaMonster (5/31/2012)
Why persist it?I added it to my code because of the following property of using Persistence:
- Any update in referenced column will be synchronized automatically in computed column if it is
Persisted.
And that's necessary over a normal computed column why?
Just wondering why everyone's recommending additional storage space and additional work on insert/update rather than no additional storage and additional work on select when there's nothing from the OP to say which would work better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2012 at 2:52 am
GilaMonster (6/1/2012)
vinu512 (5/31/2012)
GilaMonster (5/31/2012)
Why persist it?I added it to my code because of the following property of using Persistence:
- Any update in referenced column will be synchronized automatically in computed column if it is
Persisted.
And that's necessary over a normal computed column why?
Just wondering why everyone's recommending additional storage space and additional work on insert/update rather than no additional storage and additional work on select when there's nothing from the OP to say which would work better.
Yes Gail, I was wrong about Persistence. I had the notion that updated resultsets would only be shown if we mark the column as Persisted.
It works without Persisted also.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply