Weird Results

[NXArmada]

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



SELECT d.`state`, d.`zip`, l.`item`, SUM(s.`sales`) FROM demo d, line l, sales s
WHERE d.`invoice` = l.`invoice` AND
d.`invoice` = s.`invoice` AND
l.`item` LIKE '"588%' AND
l.`item` NOT LIKE '%-REC%' AND
l.`item` NOT LIKE '%-USED%'
GROUP BY d.`zip`;

[NXArmada]

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



SELECT d.`state`, d.`zip`, l.`item`, SUM(REPLACE(s.`sales`,",","")) AS `sales` FROM demo d, line l, sales s
WHERE d.`invoice` = l.`invoice` AND
d.`invoice` = s.`invoice` AND
l.`item` LIKE '"588%' AND
l.`item` NOT LIKE '%-REC%' AND
l.`item` NOT LIKE '%-USED%'
GROUP BY d.`zip`
ORDER BY `sales` DESC;