You are just counting the number of total rows. COUNT() doesn’t do a count if true or anything like that. So what you’ll want to do a sum with a case statement.

sum(
case when OrderRel.OpenRelease = true then 1
else 0
end
)

We often get asked for this kind of data as well. Remember that your unit price is at the order line level, and the quantity at the release level determines the total potential value of the sales order. Sometimes they really want that remaining value on the order, because some of it shipped out already. In this case, you have to subtract the shipped amount from the release quantity to see how much is left on each release. Multiply by your unit price to see remaining sales order value. How this actually aligns with your open jobs, and purchase orders is a while different story!
Good luck!

@Banderson what would be the Data Type (Bit? NVCHAR?)

What would this error mean?
Severity: Error, Table: , Field: , RowID: , Text: The multi-part identifier “OrderRel.OpenRelease” could not be bound.
Invalid column name ‘true’.

@Banderson good to know about “integer”. I keep receiving the following error:
Severity: Error, Table: , Field: , RowID: , Text: The multi-part identifier “OrderRel.OpenRelease” could not be bound.
Invalid column name ‘true’.
The multi-part identifier “OrderRel.OpenRelease” could not be bound.
Invalid column name ‘true’.

select distinct
(sum( SubQuery2.Calculated_OpenQty )) as [Calculated_TotalOpenQty],
(sum( SubQuery2.Calculated_OpenValue )) as [Calculated_TotalValue],
[SubQuery2].[Calculated_TotalOpenRlse] as [Calculated_TotalOpenRlse]
from Erp.OrderHed as OrderHed
inner join (select
[OrderRel1].[OrderNum] as [OrderRel1_OrderNum],
[OrderRel1].[PartNum] as [OrderRel1_PartNum],
[OrderRel1].[ReqDate] as [OrderRel1_ReqDate],
[OrderRel1].[NeedByDate] as [OrderRel1_NeedByDate],
((OrderRel1.OurReqQty - OrderRel1.OurJobShippedQty - OrderRel1.OurStockShippedQty)) as [Calculated_OpenQty],
(((case when (OrderDtl1.PricePerCode = 'M') then ((((case when (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else 0 end))/ NULLIF(1000, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0)))) else ((case when (OrderDtl1.PricePerCode = 'C') then ((((case when (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.OurStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else 0 end))/ NULLIF(100, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0)))) else ((((case when (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty - OrderRel1.SellingStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else 0 end))/ NULLIF(1, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0)))) end)) end))) as [Calculated_OpenValue],
(sum( case when OrderRel1.OpenRelease = 1 then 1 else 0 end )) as [Calculated_TotalOpenRlse]
from Erp.OrderDtl as OrderDtl1
inner join Erp.OrderRel as OrderRel1 on
OrderRel1.Company = OrderDtl1.Company
and OrderRel1.OrderNum = OrderDtl1.OrderNum
and OrderRel1.OrderLine = OrderDtl1.OrderLine
and ( OrderRel1.OpenRelease = Y and OrderRel1.OurReqQty > 0 and OrderRel1.ReqDate < @EndDate )
group by [OrderRel1].[OrderNum],
[OrderRel1].[PartNum],
[OrderRel1].[ReqDate],
[OrderRel1].[NeedByDate],
((OrderRel1.OurReqQty - OrderRel1.OurJobShippedQty - OrderRel1.OurStockShippedQty)),
(((case when (OrderDtl1.PricePerCode = 'M') then ((((case when (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else 0 end))/ NULLIF(1000, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0)))) else ((case when (OrderDtl1.PricePerCode = 'C') then ((((case when (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.OurStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else 0 end))/ NULLIF(100, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0)))) else ((((case when (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty - OrderRel1.SellingStockShippedQty) > 0 then (OrderRel1.SellingReqQty - OrderRel1.SellingJobShippedQty- OrderRel1.SellingStockShippedQty) else 0 end))/ NULLIF(1, 0)) * OrderDtl1.UnitPrice * (1 - (OrderDtl1.DiscountPercent / NULLIF(100, 0)))) end)) end))),
(sum( case when OrderRel1.OpenRelease = 1 then 1 else 0 end ))) as SubQuery2 on
SubQuery2.OrderRel1_OrderNum = OrderHed.OrderNum

I’m receiving the following error - and have tried multiple ways to get rid of it…

Severity: Error, Table: , Field: , RowID: , Text: Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 5058.7648 ms.