How to make formula based on a related table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make formula based on a related table

Author

Topic

sha_agrawal
Starting Member

24 Posts

Posted - 2009-10-19 : 11:36:17

My Regards to all experts
I have 2 tables, 1'st table containing fields
BillNo
TotalSaleAmount
Another table containing
BillNo
itemID
saleamount.
E.g. 2nd table data is
1st record
Bill No.1
ItemID 101
SaleAmount 25000
2nd record
Bill No.1
ItemID 102
SaleAmount 10000

I want to use formula for TotalSaleAmount in 1st table to contain bill wise total i.e. 35000. Please help me, how to do it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-19 : 12:30:02

select
BillNo,
sum(SaleAmount) as TotalSaleAmount
from table2
group by BillNo

No, you're never too old to Yak'n'Roll if you're too young to die.

sha_agrawal
Starting Member

24 Posts

Posted - 2009-10-20 : 10:19:45

Sorry to say 'Mr.webfred' but it raise error 'error validating the formula...'. I am using SQL server 2000.Pls.help me

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-20 : 15:40:12

can u post the exact query you used?

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-20 : 16:31:54

quote:
Originally posted by webfred

select
BillNo,
sum(SaleAmount) as TotalSaleAmount
from table2
group by BillNo

No, you're never too old to Yak'n'Roll if you're too young to die.

That was like Walking into France

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sha_agrawal
Starting Member

24 Posts

Posted - 2009-10-21 : 09:59:01

Thanks Vijay and WebFred for reply,
Below are 2 tables I am using :-
CREATE TABLE [dbo].[Purch] (
[GSNo] [int] IDENTITY (1, 1) NOT NULL ,
[TotalContainer] AS "SELECT GSNO,SUM(NoOfContainer) AS TotalContainer FROM PurchDetl GROUP BY GSNo" ,
) ON [PRIMARY]

CREATE TABLE [dbo].[PurchDetl] (
[GSno] [int] NOT NULL ,
[ItemId] [int] NOT NULL ,
[NoOfContainer] [numeric](18, 2) NOT NULL ,
) ON [PRIMARY]

PurchDetl Records:-
GSNO ItemID NoOfContainer
134 101 56
134 305 72
171 476 101
171 902 98

Both tables are related with GSNO field.

I want to use formula attribute For TotalContiner field existing in Purch table as "SELECT GSNO,SUM(NoOfContainer) AS TotalContainer FROM PurchDetl GROUP BY GSNo" so that sum of containers GS no. wise can be stored in Purch Table.
Records of Purch should be as :-
GSNO TotalContainer
134 128
171 199
GS No. will be inserted by front-end software and NoOfContainer field should be filled by the formula.

I tried to use formula attribute of TotalContainer as
"SELECT GSNO,SUM(NoOfContainer) AS TotalContainer FROM PurchDetl GROUP BY GSNo"
but it raises error "Error validating formula....."

I think it is now clear. Please help me
Please help me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources