[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL of Count of vendors paid over X amount since a specified date

Posted on 2007-07-21
6
Medium Priority
?
386 Views
Last Modified: 2013-12-07
Need help with a sql of a count of vendors that have been paid and have total amount paid of greater than $10,000 since January 1, 2006. Here is my attempt:

select count(ac.vendor_id)
from ap_checks_all ac
where ac.vendor_id in (select ac.vendor_id
                ,sum(ac.check_amount)> '10000.00'
                from ap_checks_all ac
                    ,po_vendors pv
                where ac.vendor_id = pv.vendor_id
                and ac.check_date between '01-JAN-06' and '31-JUL-07'
                group by ac.vendor_id)
group by ac.vendor_id
-----------------------------
Running 11.5.10.2 Oracle Applications on an Oracle RDBMS 9.2.0.6.0


0
Comment
Question by:janthonyn
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19540144

select count(ac.vendor_id)
from ap_checks_all ac
where ac.vendor_id in (select ac.vendor_id
                from ap_checks_all ac
                    ,po_vendors pv
                where ac.vendor_id = pv.vendor_id
                and ac.check_date between '01-JAN-06' and '31-JUL-07'
                group by ac.vendor_id
                 having sum(ac.check_amount)> 10000.00
                )
group by ac.vendor_id
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 19544993
select vendor_id,count(vendor_id)
from
(select ac.vendor_id VENDOR_ID,sum(ac.check_amount) CK_AMT
from ap_checks_all ac,po_vendors pv
where ac.vendor_id = pv.vendor_id
and ac.check_date between '01-JAN-06' and '31-JUL-07'
group by ac.vendor_id
) A
where A.CK_AMT >10000 group by vendor_id
0
 

Author Comment

by:janthonyn
ID: 19548163
Keep getting the following error for both of the above proposed solutions:

[1]:(Error):ORA-00904:"AC".CHECK_AMOUNT":invalid identifier
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 80 total points
ID: 19548256
well, do you really have a column named check_amount in the table/view ap_checks_all ?
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1920 total points
ID: 19548271
select count(*) from
(select ac.vendor_id, sum(ac.check_amt)
 from ap_checks_all ac
 where ac.check_date > to_date('01-JAN-06','DD-MON-YY')
 group by ac.vendor_id
 having sum(ac.check_amt) > 10000);
0
 

Author Comment

by:janthonyn
ID: 19549566
angellll  you are correct. The column is named "amount",  but your sql, when corrected for column name, outputs number of checks per vendor_id. awking00: your sql is the one I needed, after correcting for column name. Thanks.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month17 days, 15 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question