Home » SQL & PL/SQL » SQL & PL/SQL » Using Count in a group function
Using Count in a group function [message #199345] Mon, 23 October 2006 12:36 Go to next message
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!
Re: Using Count in a group function [message #199346 is a reply to message #199345] Mon, 23 October 2006 12:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check here Using the HAVING Condition.
Re: Using Count in a group function [message #199350 is a reply to message #199345] Mon, 23 October 2006 14:01 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And check this on DATE datatype vs. CHAR datatype.

[Updated on: Mon, 23 October 2006 14:02]

Report message to a moderator

Previous Topic: Error handling in PL\SQL
Next Topic: Analytical Function MAX
Goto Forum:
  


Current Time: Sun Dec 11 06:02:45 CST 2016

Total time taken to generate the page: 0.05899 seconds