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.
What is the value of Num, what is the total that is being returned, and what is the data types of the Num column?
Your sample data isn't consistent, and your explanation of what you are trying to calculate doesn't match up with the data you provided. For example, the first set of data has A,B,C,D,E for values in the Name field, but only A,B,C are in the second set. Then you describe the formula for calculating Total as such: The Total equals to Num multiply by itself, and add all Nums that has the same ID. But the second set of data you provide doesn't follow that. If you look at the first row you see: ID Name Num Total ----------------- 22 A 2 7 <=> (2*2)+3 But, since there are 2 records with value 22 as their ID in the first set of data you provide, your function should be represented like this: 22 A 7 <=> (2*2)+3+2 Could you correct any typos you made in the data and clarify your forumla?
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 Id: number Name: text Num: number Values of Nums are: 7 11 5 7 7 thanks.
update yourtable set Total=num*num+(select sum(num) from yourtable where id+name!=yourtable.id+yourtable.name group by ID); Something like this, you might need something else for inequality (!=) and use concat instead of +. But this could be the base for your solution, I don't have an SQL serve rat the moment so I can't test the specific syntax needed.