A bad choice of wording on my part. Yes, technically there are indeed 11 transactions and therefore $55 is indeed the sum is 5+5+5+5+5+5+5+5+5+5+5 = 55 and not the product of 11 x 5 = $55.
I do understand that the group by is essentially creating a select sum(non-inventory-items.salesprice)
However, the point remains there is no way to show the unit price when grouping by item. Remember that this price field is being pulled from the Master Data table (non-inventory items) and is a unit price. 55 is a meaningless number.
So either we need to be able to add a min, max, first, average etc. to the select statement (select first(non-inventory-item.salesprice) or we need to recognize that price fields from the master data are not transaction based values to be summed.
In summary, I agree 100% that the current result (55) is a sum not a product. However Unit Price should either never be summed or the select should allow for qualifiers (min, max, average etc).
Honestly, I simply want to get a price list for my non-inventory items. Cannot be done.
- I initially did a select where all select lines were from the non-inventory item (master data) table;
- This resulted in multiple records for each item, one per transaction, so I was ‘forced’ to group by item to get back to a single record per item;
- Having done that it summed (not multiplied) the unit price to a meaningless $55.
- I added the Qty and amount simply to highlight the issue (these are transaction based and make sense to sum for a group by).
I would email price lists directly from Manager often. That capability is now gone with the new reports.
Basically - Cutting to the root of the issue. Give us either:
- Ability to select and report on master data without it being transaction driven (by unspecified behind the scenes joins) or
- The ability to specify min, max etc. (as you could in SQL) if the base select is always forced (behind the scenes) to transaction level even when we don’t ever select a single field from transaction tables and we have no choice but to use a group statement.
I won’t share or trouble you with the ridiculous amount of grouping that had to be done to produce a simple Member Roster (customer listing) report to get it to one record per customer rather than repeating the customer record for every transaction.