I have an issue with the following. When I create a page which has a sum in the custom query and then do a search (on the search enabled fields) the search only returns like a 140 results. Now if I do the same search using the filter settings, I get all the correct results.
The sum is to sum all records with the same itemcode and warehouse id. The query looks like this:
SELECT
Partnumbers.Partnumber,
Partnumbers.PartnumberAlt AS Alternative,
Partnumbers.PartDescription AS Description,
Partnumbers.ForDevice,
Stock.StockLocation AS Location,
SUM(Stock.Amount) AS Quantity,
concat(Country.ISOCode3, ' - ', Warehouse.WarehouseDescription) AS Warehouse,
Partnumbers.Part_ID,
Stock.Stock_ID,
Partnumbers.Active,
PartType.Shortcode,
Manufacturer.ManufacturerDescription,
Partnumbers.Partnumber AS Barcode
FROM Partnumbers
RIGHT OUTER JOIN Stock ON Stock.Part_ID = Partnumbers.Part_ID
INNER JOIN Warehouse ON Stock.Warehouse_ID = Warehouse.Warehouse_ID
LEFT OUTER JOIN Country ON Warehouse.CountryID = Country.Country_ID
LEFT OUTER JOIN PartType ON Partnumbers.PartType_ID = PartType.PartType_ID
LEFT OUTER JOIN Manufacturer ON Partnumbers.Manufacturer_ID = Manufacturer.Manufacturer_ID
WHERE (Partnumbers.Active ='1')
GROUP BY Stock.StockLocation, Warehouse.WarehouseDescription
ORDER BY Partnumbers.PartDescription
Now my question is: why doesn't the sum work in this case?