Calculate Rows with Add and Multiply - Please help

Discussion in 'Databases' started by jennypretty, May 17, 2009.

  1. #1
    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.
     
    jennypretty, May 17, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    What is the value of Num, what is the total that is being returned, and what is the data types of the Num column?
     
    jestep, May 18, 2009 IP
  3. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #3
    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?
     
    plog, May 18, 2009 IP
  4. jennypretty

    jennypretty Active Member

    Messages:
    584
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #4
    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.
     
    jennypretty, May 18, 2009 IP
  5. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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.
     
    chisara, May 19, 2009 IP