Search code examples
sqlsql-servert-sqlselect

HAVING Count clauses with conditions?

Say I have a table with orders stored in it. Those orders have an OrderType column indicating what kind of order it is.

Those orders also have a column indicating which orders should be grouped together (OrderGroup).

I now need to find OrderGroups that have two OrderTypes of 3845 AND one OrderType of 5188.

I built my normal query with the GROUP BY like this:

SELECT  OrderGroup
FROM    dbo.Order orders
GROUP BY orders.OrderGroup
ORDER BY orders.OrderGroup

But now that I need to add the criteria for the OrderType rows, I am a but stumped. This is the logic I am looking for (not real SQL):

SELECT  OrderGroup
FROM    dbo.Order orders
GROUP BY orders.OrderGroup
HAVING (COUNT(orders.OrderType) = 2 WHEN orders.OrderType = 3845)
       AND (COUNT(orders.OrderType) = 1 WHEN orders.OrderType = 5188)
ORDER BY orders.OrderGroup

Is there a way to do something like this in SQL Server 2019?

Solution

  • You need to create count expressions to conditionally count the relevant orders, and then compare those to the expected counts:

    SELECT   OrderGroup
FROM     dbo.Order orders
GROUP BY orders.OrderGroup
HAVING   COUNT(CASE orders.OrderType WHEN 3845 THEN 1 END) = 2 AND
         COUNT(CASE orders.OrderType WHEN 5188 THEN 1 END) = 1
ORDER BY orders.OrderGroup