# Epicor BAQ Cookbook: Group By, Union, Distinct, Null Tests, Pivot and Subqueries > Source: https://netray.co/erp-help/epicor-baq-group-by-union-distinct-examples > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer Most BAQ patterns map to plain SQL: Group By with aggregates, Combined Queries for union, a Distinct setting, null tests in criteria or expressions, and subqueries for per-row lookups. Pivot and running totals have no single switch, so build them with conditional aggregates and subqueries. ## Kind how-to ## System epicor ## Erp Epicor Kinetic ## Module BAQ / SQL Patterns ## Query epicor baq group by ## Alternate Queries - epicor baq union - epicor baq union all - epicor baq distinct - epicor baq is null - epicor baq contains - epicor baq pivot - epicor baq running total - epicor baq subquery types ## Applies To Epicor Kinetic and 10.x Query Builder. Option names vary a little between releases, and expression support depends on the SQL Server version underneath. ## Steps ### How to apply each pattern in Query Builder **Items:** - Group By: on Display Fields mark which columns are grouped, then on the Group By tab set aggregate (Sum, Count, Min, Max, Avg) on the value columns. - Union: create the second query as a separate BAQ with the same column count and types, then add it on the Combined Queries tab; choose union or union all. - Distinct: turn on the Distinct option in the query settings, or group by every selected column. - Null tests: in Table Criteria use the null or not null condition, or a calculated field that wraps the column with ISNULL. - Contains: use the contains or like condition, or a calculated field with the T-SQL LIKE operator. - Subqueries: add on the Subqueries tab, then link by key to the main query and use as a join, a column or an exists test. - Run Analyze to see the generated SQL and compare with the pattern below. ## Sections ### Group by, distinct and null tests **Body:** Group By in a BAQ is standard SQL. Every non-aggregated display column must be in the group. Total order value by customer is a group on CustID with Sum of the line total. Distinct removes duplicate output rows and is easiest when you want a unique list, such as the list of parts that were shipped last month. For null, remember SQL treats null differently from empty text. A criteria of equals blank does not match null. Use is null or is not null, or wrap the column in ISNULL(column, 0) in a calculated field so downstream math works. Left joins create nulls from missing matches, so plan for them. **Code:** SELECT c.CustID, SUM(d.DocExtPriceDtl) AS Total FROM Erp.OrderDtl d JOIN Erp.OrderHed h ON h.Company = d.Company AND h.OrderNum = d.OrderNum JOIN Erp.Customer c ON c.Company = h.Company AND c.CustNum = h.CustNum GROUP BY c.CustID ### Union and union all **Body:** Use the Combined Queries feature when two sources have the same shape, for example open sales order demand and open job demand into one demand list. Union removes duplicate rows and sorts, union all keeps every row and is faster. Use union all unless you need to deduplicate. Columns match by position, so keep order and types identical. Add a literal column such as Source to tell which query each row came from. Sorting is applied to the combined result. ### Contains, pivot, running totals and subqueries **Body:** Contains is a LIKE on percent signs, which cannot use an index, so combine it with other filters. A pivot, such as monthly sales in columns, is built with conditional aggregates: SUM(CASE WHEN month = 1 THEN amount ELSE 0 END) per column, then grouped by customer. Dashboards with a pivot grid can also pivot at display time. A running total is not a built-in. Common approaches are a correlated subquery that sums rows up to the current date, or doing the running sum in the dashboard grid. Window functions may not be accepted in the expression editor in some releases, so test before building on them. Subquery types let you return a single value per row (a top 1 lookup), a set for in or exists tests, or an inline table to join. Keep them narrow and indexed on the key. **Code:** SELECT h.CustNum, SUM(CASE WHEN MONTH(h.OrderDate) = 1 THEN d.DocExtPriceDtl ELSE 0 END) AS Jan, SUM(CASE WHEN MONTH(h.OrderDate) = 2 THEN d.DocExtPriceDtl ELSE 0 END) AS Feb FROM Erp.OrderHed h JOIN Erp.OrderDtl d ON d.Company = h.Company AND d.OrderNum = h.OrderNum GROUP BY h.CustNum ## Pitfalls - Using union when union all would do, which adds a sort and removes valid duplicate rows. - Comparing to an empty string when the data is null. - Selecting a column not in the group by. - Using contains on a large table without any other filter. - Putting a running total subquery over millions of rows without a date filter. ## Ai Angle Writing these patterns is where an assistant helps most: describe the report and it returns the BAQ structure and the generated SQL for a developer to check, using your real table and field names. It runs read-only and shows the query. On-prem models keep your schema private. ## Faq ### How do I do a union in an Epicor BAQ? **Answer:** Build both queries as BAQs with the same columns in the same order and type, then combine them on the Combined Queries tab. Pick union all unless you need duplicates removed. ### How do I test for null in a BAQ? **Answer:** Use an is null or is not null condition in Table Criteria, or wrap the field in ISNULL in a calculated field. Equals blank does not match null. ### Can a BAQ do a pivot? **Answer:** There is no pivot switch. Use SUM(CASE WHEN ...) per column with a group by, or pivot in the dashboard grid at display time. ### How do I get a running total in a BAQ? **Answer:** Use a correlated subquery that sums values up to the current row, or compute it in the dashboard. Add date filters to keep it fast. ## Search terms epicor baq group by, epicor baq union, epicor baq union all, epicor baq distinct, epicor baq is null, epicor baq contains, epicor baq pivot, epicor baq running total, epicor baq subquery --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json