[SOLVED] SQL Select Query with Join: How to Count a Certain Number of Occurences on non-primary Attribute without a Foreign Key

SQL Select Query with Join: How to Count a Certain Number of Occurences on non-primary Attribute without a Foreign Key

Using the following Tables:

CREATE TABLE CUSTOMERS
(customerID     INT     PRIMARY KEY,
customerFullName        VARCHAR(20) NOT NULL,
customerStreet      VARCHAR(40) NOT NULL,
customerState       VARCHAR(15) NOT NULL,
customerCity        VARCHAR(20) NOT NULL,
customerZip     VARCHAR(15) NOT NULL);

CREATE TABLE VEHICLES
(vehicleVIN     VARCHAR(25) PRIMARY KEY,
vehicleType     VARCHAR(10) NOT NULL CHECK (lower(vehicleType) IN ('compact', 'midsize', 'fullsize', 'suv', 'truck')),
vehicleMake     VARCHAR(15) NOT NULL,
vehicleModel        VARCHAR(15) NOT NULL,
vehicleWhereFrom    VARCHAR(20) NOT NULL CHECK (lower(vehicleWhereFrom) IN ('maryland','virginia','washington, d.c.')),
vehicleWholesaleCost    DECIMAL(9,2)    NOT NULL,
vehicleTradeID      INT);

CREATE TABLE SALES
(saleID         INT     PRIMARY KEY,
grossSalePrice      DECIMAL(9,2),
vehicleStatus       VARCHAR(10) NOT NULL CHECK (lower(vehicleStatus) IN ('available', 'sold', 'pending')),
saleDate        DATE,
saleMileage     INT,
customerID      INT,
salespersonID       INT,
vehicleVIN      VARCHAR(25),
CONSTRAINT SALES_FK1 FOREIGN KEY (customerID) REFERENCES CUSTOMERS(customerID),
CONSTRAINT SALES_FK2 FOREIGN KEY (vehicleVIN) REFERENCES VEHICLES(vehicleVIN));

I have built the following query to display the vehicleMake that was involved in the most sales and the customerFullName of all who purchased a vehicle of that make:

SELECT VEHICLES.vehicleMake, CUSTOMERS.customerFullName
FROM SALES
JOIN CUSTOMERS on SALES.customerID = CUSTOMERS.customerID
    JOIN(
        SELECT SALES.vehicleVIN, VEHICLES.vehicleMake
        FROM SALES
          JOIN VEHICLES ON SALES.vehicleVIN = VEHICLES.vehicleVIN
          GROUP BY SALES.vehicleVIN, VEHICLES.vehicleMake
          HAVING COUNT(SALES.vehicleVIN) >= ALL
          (SELECT COUNT(SALES.vehicleVIN)
             FROM SALES
               INNER JOIN VEHICLES ON SALES.vehicleVIN=VEHICLES.vehicleVIN
               GROUP BY VEHICLES.vehicleMake))
      VEHICLES ON SALES.vehicleVIN = VEHICLES.vehicleVIN
      ORDER BY CUSTOMERS.customerFullName;

When I run this in my Oracle NOVA DB, I only get "no rows selected" as a result. I suspect this is because the following clause is counting the number of occurrences that a vehicleVIN is sold, instead of counting the number of occurrences that a vehicleMake is sold.

HAVING COUNT(SALES.vehicleVIN) >= ALL
          (SELECT COUNT(SALES.vehicleVIN)
             FROM SALES
               INNER JOIN VEHICLES ON SALES.vehicleVin=VEHICLES.vehicleVIN
               GROUP BY VEHICLES.vehicleMake))
      VEHICLES ON SALES.vehicleVIN = VEHICLES.vehicleVIN
      ORDER BY CUSTOMERS.customerFullName;

How would I change this SELECT Count subquery to count the number of vehicleMake occurences instead of the number of vehicleVIN occurrences? The output I'm looking for is:

vehicleMake     customerFullName
CAR2            Bob Jim
CAR2            Jim Bob
CAR2            Steve France
CAR2            Tom Williams
CAR2            John Johnson

It should only display the vehicleMake of the vehicleMake that was sold the most, and the customerFullName of those who purchased any vehicle of that make. Here is the link to SQL Fiddle if you want to see the Schema I've built: http://sqlfiddle.com/#!4/b0ac3a

Solution

http://sqlfiddle.com/#!4/b0ac3a/67

WITH
  tallied_sales AS
(
  SELECT
    SALES.*,
    VEHICLES.vehicleMake,
    COUNT(*) OVER (PARTITION BY VEHICLES.vehicleMake)   AS vehicleMakeSales
  FROM
    SALES
  INNER JOIN
    VEHICLES
      ON VEHICLES.vehicleVIN  = SALES.vehicleVIN
),
  ranked_sales AS
(
  SELECT
    tallied_sales.*,
    RANK() OVER (ORDER BY vehicleMakeSales DESC)   AS vehicleMakeSalesRank
  FROM
    tallied_sales
)
SELECT
  *
FROM
  ranked_sales
INNER JOIN
  CUSTOMERS
    ON CUSTOMERS.customerID = ranked_sales.customerID
WHERE
  ranked_sales.vehicleMakeSalesRank = 1
;