Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vack
Aged Yak Warrior
530 Posts |
 Posted - 2011-02-18 : 11:24:15
|
Working with a table that looks like this:Table Name: oeordlinOrd_no Prod_Cat222 511222 503444 512444 503555 511666 503 What I want to do is have a select statement that will count the number of orders. If a order has a 511 or 512 on it its only counted once. In the example above for order 222 I do not want Prod_Cat 503 counted since there is a 511 on the order. Below is what I would expect returned. Is this possible?Prod_cat Count511 2512 1503 1 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-18 : 13:07:18
|
yes it is possible. at least you may get an idea via below  declare @t table (Ord_no int, Prod_Cat int)insert into @t values (222,511),(222,503),(444,512),(444,503),(555,511),(666,503)Select prod_Cat,Count(distinct Ord_no) from @t where prod_cat in (511,512) group by prod_catunion all Select prod_Cat,Count(distinct prod_Cat) from @t where prod_cat not in (511,512) group by prod_cat |
 |
|
|
|
|
|
|
|
Select Count