AX query with group, sum statment

hi,

I would like get from DB:

itemId, ItemName, sum(availPhysical)

I wrote below query but I can’t get data from inventTable.Record is empty? Why?

Query query;
QueryRun queryRun;
QueryBuildDataSource qbdsIT, qbdsIM, qbdsIS;
InventTable inventTable;
InventSum inventSum;
;

query = new Query();

qbdsIT = query.addDataSource(tablenum(inventTable));
qbdsIT.addRange(fieldnum(InventTable, ItemGroupId)).value(“01”);

qbdsIS = qbdsIT.addDataSource(tablenum(InventSum));
qbdsIS.addSelectionField(fieldNum(InventSum, AvailPhysical), SelectionField::Sum);
qbdsIS.addGroupByField(fieldnum(InventSum, ItemId));
qbdsIS.relations(true);
qbdsIS.joinMode(JoinMode::InnerJoin);

queryRun = new QueryRun(query);

while (queryRun.next())
{
inventSum = queryRun.get(tablenum(InventSum));
inventTable = queryRun.get(tablenum(InventTable));

info(strfmt(“Indeks %1, ilosc %2”, inventTable.ItemId, inventSum.AvailPhysical));
}

If you don’t get any records at all, it means that you don’t have any items with item group “01” and records in InventSum.

By the way, your code won’t work as expected anyway because you group by InventSum.ItemId but try to read the value from InventTable.ItemId.

Info log return below data. There are ItemId in this group.

so how I should write the query? I have to read many value from inventTable (itemName, PrimaryVendorId …)

SELECT FIRSTFAST * FROM InventTable GROUP BY InventSum.ItemId WHERE ((ItemGroupId = N’01’)) JOIN FIRSTFAST SUM(AvailPhysical) FROM InventSum WHERE InventTable.ItemId = InventSum.ItemId

ItemId , Avail 1.00,
ItemId , Avail 0.00,
ItemId , Avail 0.00,
ItemId , Avail 2.00,
ItemId , Avail 1.00,
ItemId , Avail 1.00,
ItemId , Avail 0.00,
ItemId , Avail 2.00,
ItemId , Avail 0.00,
ItemId , Avail 0.00,
ItemId , Avail 1.00,
ItemId , Avail 0.00,
ItemId , Avail 1.00,
ItemId , Avail 0.00,
ItemId , Avail 0.00,

If a field is not used for grouping, it can have (in general) multiple values and you have to say the database what to do with them - you can select one value (min/max) or aggregate multiple values together (sum/count/avg).

If a field is not used for either for grouping or aggregation, its value is not defined.