I’m trying to create a subquery to count the number of unique customers for a given part(amongst several other aggregate calculations so this needs to be in a subquery). I found how to set a subquery to return distinct rows, and when I return all rows I see they are indeed only the distinct/unique rows. Problem is when I create a calculated field to COUNT(custNum) my count is for all rows not just the distinct/unique rows.
1 Like
Why not select distinct CustNums, and then your calculated field is count(*)?
@Aaron_Moreng How do I do that? I just found how to return distinct rows from another post on this forum(Subquery options, Result Set Rows: Distinct) 
Or maybe you are suggesting moving the calculated field up to the TopLevel query and just display the distinct custNums. If so I found that works for just this subquery but as soon as I introduce other subqueries(e.g. calcaulate number of orders, qty shipped, $ shipped, etc) it introduces more rows so my calculation at the top level for unique customers breaks.
Aggregates are sensitive the the grouped by columns, so you would simply select your CustNum as a group by field and then calculate count(*). Adding PartNum in there and grouping by would also group by the PartNums.
@Aaron_Moreng For context I’m trying to create a BAQ that returns one row per part with Qty Shipped, $ shipped, num unique customers, num unique orders.
If I select distinct CustNum and count(*) at the toplevel it works correctly. Problem is that is only one of the fields I want the toplevel BAQ to return. As soon as I try to do the same with say numOrders my numCustomers calculation breaks. I know I could solve this with multiple BAQs, but I’m trying to use this as an opportunity to learn more about the BAQ tools. Any suggestions?
Hi Eddie,
You might create a subquery for each of the aggregates you want that includes the part number (qty, dollars, customers, orders) and then outer link them back to the Part master. If you want to exclude parts with no activity, you could add a subquery selection.
Mark W.
2 Likes
@Mark_Wonsil that is exactly what I’m trying to do, problem is my count distinct doesn’t seem to work in the subquery. If I display all the customers in the subquery for example I see only the distinct rows so I assume my subquery and the distinct clause is working, but as soon as I add an calculated field to the subquery I just verified as working(only returns distinct rows) the count is for all rows.
I don’t understand why but I think what @Aaron_Moreng is suggesting is as follows for my subqueries.
What if you replace count(*) with SUM(1)?
Both return the same data - 7 rows which has me scratching my head why I’m getting more than 1 row.
And in the subquery options you have distinct, right? Sometimes I like to structure my query first to see what it looks like before I aggregate, just to eliminate any oddities
2 Likes
Try
-- Actually put the word DISTINCT inside your Count
COUNT(DISTINCT ShipDtl_Customers.CustNum)
4 Likes