Post reply

how to Mutilply different data type values

  • February 29, 2008 at 4:33 am

    #71587

    Hi ,

    I have the below tables

    Table 1 with columns

    id int

    price char

    quantity int

    date date

    In this table, same id has several entries at different date.

    table 2 with columns

    groupid int

    groupname char

    table 3 with columns

    id int

    groupid int

    I want the result as

    id

    groupname

    sum(quantity * price) for each id

    Please help me in writing a query for this

    Thanks in advance

  • February 29, 2008 at 4:47 am

    #783678

    This looks a lot like homework... what have you tried?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 29, 2008 at 5:03 am

    #783685

    I have tried starting with the below query

    select t3.id, t2.groupname, t1.quantity, t1.price,t1.date from table3 t3

    join table2 t2 on t2.groupid=t3.groupid

    join executions_history t1 on t1.accountid=t3.accountid

    where

    t2.groupname='s1'

    In result i need the sum(quantity * price ) of each id

    But since quantity and price are different data type im geeting error

    Server: Msg 245, Level 16, State 1, Line 1

    Syntax error converting the varchar value '2.8200 ' to a column of data type int.

    I need to multiply both columns and use curson to sum the product for each users.

    I need help in writing this as im a newbie in programming.

    Thanks

  • February 29, 2008 at 5:39 am

    #783692

    The problem is that you have the Price defined as a CHAR column... it should probably be the MONEY data type if you want the 4 decimal places.

    If you can't change the column (and you really should) to a correct numeric datatype, then you will have to CAST the column to a correct numeric data type as part of your multiplication formula...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply