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

Thanks Rob. I just added it to my statement.

Regards,

PETER VIDAL
MAPICS IT TECHNICAL SUPPORT TEAM / SR SYSTEM ANALYST
10540 Ridge Rd., Suite 203, New Port Richey, FL 34654-5111
Tel:727-849-9999, x2414 Fax:727-815-3120
WWW.PALL.COM

"Perpetual optimism is a force multiplier."
Colin Powell (1937- )




rob@xxxxxxxxx
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/22/2009 04:18 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

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






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