Calculate Rows with Add and Multiply - Please help

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

SQL Server 2000 Forums

SQL Server Development (2000)

Calculate Rows with Add and Multiply - Please help

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2009-05-14 : 08:29:23

I have a dynamic query:

ID Name Num
------------
22 A 2
22 B 3
23 C 1
23 D 2
23 E 2

How to create a dynamic query to calculate the total like below?

ID Name Num Total
-----------------
22 A 2 7 <=> (2*2)+3
22 B 3 11 <=> (3*3)+2

23 A 1 5 <=> (1*1)+2+2
23 B 2 7 <=> (2*2)+1+2
23 C 2 7 <=> (2*2)+2+1

The Total equals to Num multiply by itself, and add all Nums that has the same ID.
Total=(Num*Num)+Nums Where ID = ID

Here is my query:
Select ID, Name, Num, (Num*Num)+Num as Total
Where ID = 1

My query returns incorrect Total.

Can you please help?

Thanks.

There is no stupid question.
www.single123.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 13:42:31

if you want Access query post this in Access forum. In sql server, this can be done like below


SELECT t.ID,
t.Name,
t.Num,
POWER(t.Num,2) + (SELECT SUM(t.Num) FROM Table WHERE ID=t.ID AND Name<> t.Name) AS Total
FROM Table t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources