Re: SQL: How to get number of records (based on a prefix)

[rob@xxxxxxxxx]

I use different field names and table name.  But using this same statement
WITH PREFIX AS 
(SELECT DISTINCT ITemnumber,SUBSTR(SiTeID,1,1) FROM pv) 
SELECT itemnumber,COUNT(*) FROM PREFIX ORDER BY Itemnumber
Dies for me with a different error, which I fully expected since you're 
doing a COUNT with no GROUP BY.  Heck, even if that worked, how could it 
be anything else but 1?  The error I get is
Column ITEMNUMBER or expression in SELECT list not valid.
which is telling me that, since you have no group by, then all you should 
get is one result for the grand total.  And the grand total can't use a 
column name on a summary function (like count).

If I change it to the following, with an appropriate group by, then it 
works
WITH PREFIX AS 
(SELECT DISTINCT ITemnumber,SUBSTR(SiTeID,1,1) FROM pv)
SELECT itemnumber,COUNT(*) FROM PREFIX 
GROUP BY ITEMNUMBER 
ORDER BY Itemnumber 

Rob Berendt