CODEWITHSUNDEEP
sqloracle-databasejoinmax
StevenC
StevenC

Reputation: 109

How to implement MAX into ORACLE SQL SELECT statement with INNER JOIN?

I built the following SELECT statement to pull from two different tables.

The tables are FINANCING_PLANS and SALES_FINANCINGS.

The Query pulls the institution, loanType, and planID from FINANCING_PLANS and uses an INNER JOIN between SALES_FINANCINGS ON FINANCING_PLANS with the JOIN taking place on the planID. Basically, this query produces a list of the institution, loanType, and planID that were used for all of the sales.

Query:

SELECT FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID,
COUNT(SALES_FINANCINGS.planID)
FROM FINANCING_PLANS
INNER JOIN SALES_FINANCINGS ON FINANCING_PLANS.planID=SALES_FINANCINGS.planID
GROUP BY FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID
HAVING COUNT(SALES_FINANCINGS.planID) >= 1;

The current output from the query looks like this:

INSTITUTION                    LOANTYPE       PLANID     COUNT(SALES_FINANCINGS.PLANID)

PNC Bank                       New                 7                              6
Navy Federal Credit Union      New                 3                              4
Bank of America                Used                1                              7
Capital One Bank               new                 4                              1
Big T Lenders                  New                 5                              2

I'm struggling with implementing a MAX into this query so the list will only reflect the financing plan that was used the most. I can't use a limit, since it will not allow for a tie in the event that two plans were utilized the same amount of times. I've tried adding MAX to the following line:

HAVING COUNT(SALES_FINANCINGS.planID) = MAX;

but it returns an error. I've also tried this:

SELECT FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID,
MAX(SALES_FINANCINGS.planID)
FROM FINANCING_PLANS
INNER JOIN SALES_FINANCINGS ON FINANCING_PLANS.planID=SALES_FINANCINGS.planID
GROUP BY FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID;

but that only lists the maximum number for the planID, not the maximum number of times it was used. It also doesn't limit it to only the maximum values.

Where and how can I incorporate MAX so I can only see a list that looks like this?

INSTITUTION                    LOANTYPE       PLANID     COUNT(SALES_FINANCINGS.PLANID)

PNC Bank                       New                 7                              6

Upvotes: 0

Views: 83

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

Use ALL in HAVING

SELECT FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID,
        COUNT(SALES_FINANCINGS.planID)
FROM FINANCING_PLANS
INNER JOIN SALES_FINANCINGS ON FINANCING_PLANS.planID=SALES_FINANCINGS.planID
GROUP BY FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID
HAVING COUNT(SALES_FINANCINGS.planID) >= ALL
(
    SELECT COUNT(SALES_FINANCINGS.planID)
    FROM FINANCING_PLANS
    INNER JOIN SALES_FINANCINGS ON FINANCING_PLANS.planID=SALES_FINANCINGS.planID
    GROUP BY FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID  
)

or using CTE to avoid duplication of the SQL code

WITH CTE_FINANCING_PLANS AS
(
    SELECT FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID,
            COUNT(SALES_FINANCINGS.planID) count_sales
    FROM FINANCING_PLANS
    INNER JOIN SALES_FINANCINGS ON FINANCING_PLANS.planID=SALES_FINANCINGS.planID
    GROUP BY FINANCING_PLANS.institution, FINANCING_PLANS.loanType, FINANCING_PLANS.planID
)
SELECT *
FROM CTE_FINANCING_PLANS
WHERE count_sales >= ALL
(
    SELECT count_sales FROM CTE_FINANCING_PLANS
)

Upvotes: 1

Related Questions