colt colt - 1 month ago 11
SQL Question

iReport not accepting SQL SUM function

iReport does not seem to accept the normal SQL SUM function and I am having a hard time figuring out a way around this.

I am trying to use

SUM(qtytofulfill.SOITEM - qtyfulfilled.SOITEM) AS qty
and it does not seem to like that or me simply adding the variables and saying
SUM(qtytofulfill - qtyfulfilled) AS qty
.

This does not seem to be a syntax error but iReport simply will not accept it as an SQL statement. I am posting a picture of me attempting to use this SQL statement and the error it also gives. Any help on what I am doing or even what I actually should be using , specifically, for iReport is greatly appreciated.

Thanks!

-Colt

enter image description here

Answer

This will work fine,

In standard SQL (but not MySQL), when you use GROUP BY, you must list all the result columns that are not aggregates in the GROUP BY clause.

SELECT
         SOITEM.'QTYFULFILLED' AS QTYFULFILLED,
         SOITEM.`QTYTOFULFILL` AS QTYTOFULFILL,
         SOITEM.`SOID` AS SOITEM_SOID,
         SUM(SOITEM.`QTYFULFILLED`) AS Sum_Quantity_Fullfilled,
         SUM(SOITEM.`QTYTOFULFILL`) AS Sum_Quantity_to_Fullfill,
         (SUM(SOITEM.`QTYFULFILLED`) - SUM(SOITEM.`QTYTOFULFILL`)) AS QTY,
         SO.`ID` AS SO_ID
         FROM
         `SO` SO INNER JOIN `SOITEM` SOITEM ON SO.`ID` = SOITEM.`SOID`
         GROUP BY SOITEM.'QTYFULFILLED',SOITEM.`QTYTOFULFILL`,SOITEM.`SOID`,SO.`ID`

Hope this helps.

Comments