how about: create view vViewname as select ItemNumber, count(distinct storenumber) from hdpastsales group by itemnumber
Karen > I am having a brain freeze. I need to create a view on a table that > provides the number of stores > that sell each item. The table has 1,500,000 rows. There are about 156 > items that are sold in any number of stores. I need to create a view that > lists each item with how many unique stores it is sold in. > Table is HDPASTSALES with StoreNumber and ItemNumber as the columns of > interest. So the view should result in a data set showing 156 rows, one for > each > item with the total number of unique stores each item was sold in. (Not the > total number of sales records, but number of unique stores.) > > I appreciate any assistance as I am not remembering the syntax . (What > makes it worse is I know I have > done this before!) > > Thanks, > -Bob >
