Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: negative integer result = 18,446,744,073,709,549,568

    hi,
    can you please help me...
    im getting this number when the result is negative

    Code:
    18,446,744,073,709,549,568
    my script is
    Code:
    SELECT ALL
      `Date`
     ,Branch
     ,currency
     ,CAST( CAST( SUM(Deposit) AS DECIMAL(10,2) ) AS UNSIGNED  ) AS 'Deposit'
     , CAST( CAST( SUM(Withdraw) AS DECIMAL(10,2) ) AS UNSIGNED ) AS 'Withdraw'
     , CAST( CAST( SUM(CashOnBank) AS DECIMAL(10,2) ) AS UNSIGNED ) AS 'CashOnBank'
     , CAST( CAST( SUM(CashOnHand) AS DECIMAL(10,2) ) AS UNSIGNED ) AS 'CashOnHand'
     , SUM(CashIn) AS 'CashIn'
     , SUM(CashOut) AS 'CashOut'
      , CAST( CAST( SUM(AmtDep) AS DECIMAL(10,2) ) AS UNSIGNED ) AS 'AmtDep'
      ,  ( SUM(CashOnHand) + SUM(CashIn) ) - ( SUM(CashOut) + SUM(AmtDep) )  AS 'TotalCashOnHand'
    FROM gen_cashinout
    
    GROUP BY
      `Date`
      ,Branch
      ,currency
    anyone please....

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where did you get the idea that you had to use doubly-nested CASTs like that?

    take them out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    i dont know who but in this forum
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by homer.favenir
    i dont know who but in this forum
    and so..... ?

    what happened when you tooked dem out?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    my field is UKNOWN type and it didnt sum up...
    what i did is cast it to make it an integer...
    how can i make it an integer?
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by homer.favenir
    my field is UKNOWN type and it didnt sum up...
    what i did is cast it to make it an integer...
    how can i make it an integer?
    my sincere advice is to redesign the table so that you store numbers in numeric columns

    in the meantime, you must perform the CAST inside the SUM, not outside
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    ok,
    thanks
    i updated my script in php and change the query
    Code:
    SELECT
    `Date`
    ,Branch
    ,currency ,
    SUM(Deposit) AS 'Deposit'
    ,SUM(Withdraw) AS 'Withdraw'
    , SUM(CashOnBank) AS 'CashOnBank'
    , SUM(CashOnHand) AS 'CashOnHand'
    , SUM(CashIn) AS 'CashIn'
    , SUM(CashOut) AS 'CashOut'
    , SUM(AmtDep) AS 'AmtDep'
    , ( SUM(CashOnHand) + SUM(CashIn) ) - ( SUM(CashOut) + SUM(AmtDep) ) AS 'TotalCashOnHand'
    FROM gen_cashinout GROUP BY `Date` ,Branch ,currency ORDER BY `Branch` ASC, `currency` ASC,`Date` DESC
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    amd did you consider changing the data types to umeric (Im guessing becuase its cash then the data type should be decimal...
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    gen_cashinout is a VIEW table
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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