Help in solving queries.......

Discussion in 'ASP .Net' started by Patrick, Jan 26, 2005.

  1. Patrick

    Patrick Guest

    Hi Group

    I want to divide one column by another column and stored that value into
    third column.

    Example :
    Column A, Column B, Column C

    I want to divide Column B by Column C and stored that value within Column A

    Basically Column A is %
    Column B < Column C ( always )

    When I use division "/" operator it returns 0 into the Column A

    Update Table Set Column A = Column b/Columb C
    it gives 0 (zero) in column A
    Let me know what I am doing wrong

    Column A : float
    column b and column c are int
    I want to see % in the column A


    TIA
    Marmik
    Patrick, Jan 26, 2005
    #1
    1. Advertising

  2. First, to see what the number of decimal places that are allowed in Column A.
    My guess is you have it set to zero. Second, you equation results in a
    decimal value. Multiple by 100 to get percent.

    "Patrick" wrote:

    > Hi Group
    >
    > I want to divide one column by another column and stored that value into
    > third column.
    >
    > Example :
    > Column A, Column B, Column C
    >
    > I want to divide Column B by Column C and stored that value within Column A
    >
    > Basically Column A is %
    > Column B < Column C ( always )
    >
    > When I use division "/" operator it returns 0 into the Column A
    >
    > Update Table Set Column A = Column b/Columb C
    > it gives 0 (zero) in column A
    > Let me know what I am doing wrong
    >
    > Column A : float
    > column b and column c are int
    > I want to see % in the column A
    >
    >
    > TIA
    > Marmik
    >
    >
    >
    =?Utf-8?B?Zmluc3RlcjI2?=, Jan 26, 2005
    #2
    1. Advertising

  3. You have to convert A and C to float before doing the division, otherwise
    only the result of an integer division is converted to float before the
    storage in A:

    A = Convert (float, B) / Convert (float, C)

    Don't forget to multiply by 100 if you want a % value.

    S. L.

    "Patrick" <> wrote in message
    news:OPx5zY$...
    > Hi Group
    >
    > I want to divide one column by another column and stored that value into
    > third column.
    >
    > Example :
    > Column A, Column B, Column C
    >
    > I want to divide Column B by Column C and stored that value within Column
    > A
    >
    > Basically Column A is %
    > Column B < Column C ( always )
    >
    > When I use division "/" operator it returns 0 into the Column A
    >
    > Update Table Set Column A = Column b/Columb C
    > it gives 0 (zero) in column A
    > Let me know what I am doing wrong
    >
    > Column A : float
    > column b and column c are int
    > I want to see % in the column A
    >
    >
    > TIA
    > Marmik
    >
    Sylvain Lafontaine, Jan 26, 2005
    #3
  4. Patrick

    John Vinson Guest

    On Wed, 26 Jan 2005 16:27:10 -0600, "Patrick" <>
    wrote:

    >Hi Group
    >
    >I want to divide one column by another column and stored that value into
    >third column.


    Storing derived data such as this in your table accomplishes
    three things: it wastes disk space; it wastes time (almost
    any calculation will be MUCH faster than a disk fetch); and
    most importantly, it risks data corruption. If one of the
    underlying fields is subsequently edited, you will have data
    in your table WHICH IS WRONG, and no automatic way to detect
    that fact.

    Just redo the calculation whenever you need it, either as a
    calculated field in a Query or just as you're now doing it -
    in the control source of a Form or a Report textbox.

    >Example :
    >Column A, Column B, Column C
    >
    >I want to divide Column B by Column C and stored that value within Column A
    >
    >Basically Column A is %
    >Column B < Column C ( always )
    >
    >When I use division "/" operator it returns 0 into the Column A
    >
    >Update Table Set Column A = Column b/Columb C
    >it gives 0 (zero) in column A
    >Let me know what I am doing wrong
    >
    >Column A : float
    >column b and column c are int


    That's the problem. An int divided by an int gives you an int.

    Try using a Query to dynamically calculate A:

    A: Csng() / Csng([c])

    If you insist you can use the same expression in your update query,
    but don't complain if you end up with INVALID DATA in your database!

    John W. Vinson[MVP]
    John Vinson, Jan 26, 2005
    #4
  5. Patrick

    Steve Kass Guest

    John,

    To give Patrick the benefit of the doubt, note that he
    mentioned needing the quotient "for historical purpose .. later."
    It might be completely reasonable to update (i.e., set) the quotient
    column A in an insert trigger, so that column A stores the original
    ratio of B to C.

    From what Patrick has said, I'd assume the value of column A
    represents "initial B/C ratio," not "current B/C ratio." The latter,
    which he may or may not need, can be calculated on the fly as
    you note.

    Another reason to store a computed value is when data is
    archived, if there is an advantage to indexing the computed
    column. Indexed views meet some of these kinds of
    needs, but they are more restrictive and less simple. And
    the data integrity an indexed view provides can be insured
    with a table constraint of CHECK (A = 1.00*B/C), though
    I'd only recommend this if the quotient were stored as a
    decimal type, not a float.

    Steve Kass
    Drew University


    John Vinson wrote:

    >On Wed, 26 Jan 2005 16:27:10 -0600, "Patrick" <>
    >wrote:
    >
    >
    >
    >>Hi Group
    >>
    >>I want to divide one column by another column and stored that value into
    >>third column.
    >>
    >>

    >
    >Storing derived data such as this in your table accomplishes
    >three things: it wastes disk space; it wastes time (almost
    >any calculation will be MUCH faster than a disk fetch); and
    >most importantly, it risks data corruption. If one of the
    >underlying fields is subsequently edited, you will have data
    >in your table WHICH IS WRONG, and no automatic way to detect
    >that fact.
    >
    >Just redo the calculation whenever you need it, either as a
    >calculated field in a Query or just as you're now doing it -
    >in the control source of a Form or a Report textbox.
    >
    >
    >
    >>Example :
    >>Column A, Column B, Column C
    >>
    >>I want to divide Column B by Column C and stored that value within Column A
    >>
    >>Basically Column A is %
    >>Column B < Column C ( always )
    >>
    >>When I use division "/" operator it returns 0 into the Column A
    >>
    >>Update Table Set Column A = Column b/Columb C
    >>it gives 0 (zero) in column A
    >>Let me know what I am doing wrong
    >>
    >>Column A : float
    >>column b and column c are int
    >>
    >>

    >
    >That's the problem. An int divided by an int gives you an int.
    >
    >Try using a Query to dynamically calculate A:
    >
    >A: Csng() / Csng([c])
    >
    >If you insist you can use the same expression in your update query,
    >but don't complain if you end up with INVALID DATA in your database!
    >
    > John W. Vinson[MVP]
    >
    >
    Steve Kass, Jan 27, 2005
    #5
  6. Patrick

    John Vinson Guest

    On Wed, 26 Jan 2005 21:11:30 -0500, Steve Kass <> wrote:

    >John,
    >
    > To give Patrick the benefit of the doubt, note that he
    >mentioned needing the quotient "for historical purpose .. later."
    >...


    Good points, all, Steve. Thanks for the reality check!

    I see quite a few folks who assume that data must be stored in a table
    in order to see it or print it or do anything with it, and this is
    usually not true (or not a good enough reason to store it) - but
    you're right, there are cases where you do need to store it.

    Just FWIW, while Decimal would be ideal for SQL, it's not implemented
    very well in the current release of Access. Currency is the closest.

    John W. Vinson[MVP]
    John Vinson, Jan 27, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. msnews.microsoft.com
    Replies:
    1
    Views:
    373
    Ollie Riches
    Feb 15, 2005
  2. Its_Me_SunnY

    Help Me in solving the queries...SUNNY

    Its_Me_SunnY, May 24, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    439
    Its_Me_SunnY
    May 24, 2005
  3. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,475
    Crutcher
    Feb 26, 2006
  4. Lionel
    Replies:
    14
    Views:
    1,140
  5. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    341
    Aaron [SQL Server MVP]
    Aug 6, 2004
Loading...

Share This Page