asked
count distinct number from only one source
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’
Please indicate helpful answers by clicking on the thumbs up next to them. If any one answer helped to solve your problem, please indicate that by clicking on the check mark next to that answer.
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 )
You'll need to take the Count distinct out of the sub query or SQL Server will complain about multiple columns being returned, it's not needed for the query to work..
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.
17 People are following this question.
