Results 1 to 2 of 2

Thread: Weird Results

  1. #1
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default Weird Results

    Dang have not been here in a year and the MySQL section is dead. Not one new post lets change that today.

    Okay I am trying to get a sum of numbers in one column but instead I am getting this.

    State | ZIP | ITEM | SALES
    '"MA"', '"01082"', '"588"', 4

    I should be getting this:

    State | ZIP | ITEM | SALES
    '"MA"', '"01082"', '"588"', 5823.8

    My MySQL Query

    PHP Code:
    SELECT d.`state`, d.`zip`, l.`item`, SUM(s.`sales`) FROM demo dline lsales s
    WHERE d
    .`invoice` = l.`invoice` AND
    d.`invoice` = s.`invoice` AND
    l.`itemLIKE '"588%' AND
    l.`itemNOT LIKE '%-REC%' AND
    l.`itemNOT LIKE '%-USED%'
    GROUP BY d.`zip`; 
    Last edited by NXArmada; 10-06-2009 at 02:41 PM.
    Ryan
    Sevierville, TN

  2. #2
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    Heres the solution REPLACE(`sales`,",","") . Turns out the Commas in the number where causing the problem so I used REPLACE to remove the comma then SUM the un-comma number SUM(REPLACE(`sales`,",","")) then used AS so that the column title was sales and not SUM(REPLACE(`sales`,",",""))

    Below is my revised working query


    PHP Code:
    SELECT d.`state`, d.`zip`, l.`item`, SUM(REPLACE(s.`sales`,",","")) AS `salesFROM demo dline lsales s
    WHERE d
    .`invoice` = l.`invoice` AND
    d.`invoice` = s.`invoice` AND
    l.`itemLIKE '"588%' AND
    l.`itemNOT LIKE '%-REC%' AND
    l.`itemNOT LIKE '%-USED%'
    GROUP BY d.`zip`
    ORDER BY `salesDESC
    Ryan
    Sevierville, TN

Bookmarks

Posting Permissions

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