Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    7

    Unanswered: SQL Query Problem...

    Hi,

    Is there any ways that I can use to query from postgresql to calculate a total sale by multiplying values from 2 columns. But the problem is, on of the column is a character type. Can I convert the value of character into integer or float in the query statement so that I can do calculation?

    ex:

    select (qty*price) from offer_item;

    qty - character varying(10)
    price - numeric(12,2)

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    conversion

    I don't know exactly the which conversion to what is allowed

    but the syntax is like that

    select (qty::numeric*price) from offer_item;

    or qty::integer and so on
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    May 2002
    Posts
    7
    hi eperich,

    thank you for you respond. I try you solution but it returns error "Cannot cast type 'character varying' to 'numeric'".

    any idea?


    Quote Originally Posted by eperich
    I don't know exactly the which conversion to what is allowed

    but the syntax is like that

    select (qty::numeric*price) from offer_item;

    or qty::integer and so on

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    change datatype

    what data do you have in qty
    I think it's quantity

    So You have already a database design error use numeric oder interger for the quantitity

    it is the same if you 'free'*7.8
    sounds not so good

    My advice change the datatype.
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •