I am trying to capture the sales of a specific salesrep for 2021 and 2022. I need all items and it’s price/qty. When I run my baq, I get duplicate parts with different prices. I need all of the parts to be rolled up so the qty and price columns get added up and the unique part number only shows up once. How can I make this happen?
Sum your extended price and qty columns by grouping by Part#.
I get a Bad SQL statement error when I do that
If I run it without grouping by Part#, it works fine but shows duplicates
What would a proper BAQ setup for this look like? I am linking the SalesRep->InvcHead->InvcDtl
Do you have a way to run the SQL outside of the BAQ Editor? This will tell you why SQL things it’s bad SQL. Although it usually has to do with data types - doing string functions on numbers or math on strings.
Start small - drop some fields form your query, stick to only SalesRep, Part# and Sum(Qty), get the group by to work like this, then add other fields and make sure the right boxes are checked on the “Display Fields” tab over on the right side for the group by.