MySQL not returning values from NOT IN function-CodePudding

I currently am trying to write a query that shows customers with at least 5 orders and customer with no orders. Orders are tracked in their own table and in order to find customers with 0 orders we have to find the customers NOT IN orders. Below is my query I'm trying to use and it returns the same customer 5 times for zero orders.

with t1 as
 (select o.customerNumber, c.customerName, count(o.orderNumber) as FiveOrders
 from orders o join customers c on (o.customerNumber = c.customerNumber)
 group by o.customerNumber having count(o.orderNumber) = 5),
 t2 as
 (select distinct o.customerNumber, c.customerName, count(o.orderNumber) as NoOrders
 from orders o join customers c on (o.customerNumber = c.customerNumber)
 group by c.customerNumber not in(select customerNumber from orders))
 select distinct t1.customerNumber as FiveOrderNumber, t1.customerName as FiveOrderName,
 t2.customerNumber as NoOrderNumber, t2.customerName as NoOrderName
 from t1 join t2
 order by NoOrderName;

Any and all help is appreciated thanks!

CodePudding user response：

If the errors were only in the second table to, I think it is after using having with condition NOT IN without any logical comparison, I think you can get wanted results easily like:

select distinct customerNumber, customerName, "0" as NoOrders 
from customers 
where customerNumber not in (Select customerNumber from orders)

If the group by is important, you can use it like in your code.

CodePudding user response：

Zero or five could be counted together with LEFT JOIN

select c.customerNumber, max(c.customerName) customerName, count(o.orderNumber) as FiveOrdersOrZero
from customers c
left join orders o on o.customerNumber = c.customerNumber
group by c.customerNumber 
having count(o.orderNumber) in ( 0, 5 )
order by FiveOrdersOrZero