I have a orders table which has order number and where the order is generated from. The order number field has duplicate records and the source field can be from multiple files. I want to count distinct number of orders which generated from only one source. If an order generated from two source then I want to discard that. I tried the below code but it returned the distinct order number with other sources as well. Select count(distinct orderNum) from tableName where source=’FileName’
Hi Would something like this work? (basically using a sub query to exclude all order numbers that come from more than one source) SELECT COUNT(DISTINCT orderNum) FROM tableName WHERE orderNum NOT IN ( --we want to exlude these order numbers as they come from multiple sources SELECT orderNum, COUNT(DISTINCT source) FROM tableName GROUP BY orderNum HAVING COUNT(DISTINCT source) > 1 )
SELECT COUNT(orderNum) FROM tableName WHERE orderNum IN (SELECT orderNum, COUNT(DISTINCT source) FROM tableName GROUP BY orderNum HAVING COUNT(DISTINCT source) = 1) I guess, Using IN operator is faster than Using NOT IN operator, please correct if I am wrong.