OraFAQ Forum: SQL & PL/SQL » Using Count in a group function

Home » SQL & PL/SQL » SQL & PL/SQL » Using Count in a group function

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using Count in a group function [message #199345]

Mon, 23 October 2006 12:36

kelliherj
Messages: 11
Registered: October 2006
Location: Killarney

Junior Member

Here is the code I want to run.
Basically I want to be able to count the number of suppliers who have a certain amount of invoices.
So say 10,000 Suppliers have between 1 and 10 invoices.

Here is my first attempt:

SELECT '1 to 10' "No of Invoices by Range", count(a.VENDOR_ID) "No Suppliers In Range"
FROM AP_INVOICES_ALL a, PO_VENDORS p
WHERE a.VENDOR_ID = p.VENDOR_ID
AND a.CREATION_DATE BETWEEN '01-OCT-05' AND '30-SEP-06'
AND p.VENDOR_TYPE_LOOKUP_CODE = 'VENDOR'
AND count(a.INVOICE_ID) >= 1
AND count(a.INVOICE_ID) < 10

But the count raises a group function is not allowed error.
Any suggestions would be greatly appreciated!

Report message to a moderator