I am trying to get the vehicle_Code from the Vehicles table which is a sequential number for a distinct make and model combination. The description of the Vehicles table is the make and model concatenation. The VIN in the Sales table aligns with the make and model of the OTLP_Vehicles table. I am unsure how to modify my JOIN and possibly add a WHERE clause to get the vehicle_Code.
Here is my current script:
SELECT sale_date,vehicle_Code,sf.plan_ID,dealer_ID,
COUNT (*) AS vehicles_sold,
SUM (s.gross_sale_price) AS gross_sales_amt
FROM Sales s
INNER JOIN Sales_Financings sf
on sf.sale_ID = s.sale_ID
GROUP BY sale_date, vehicle_Code, sf.plan_ID, dealer_ID;
Describes of the tables I need to use for the vehicle_Code:
SQL> desc Sales
Name Null? Type
----------------------------------------- -------- ----------------------------
SALE_ID NOT NULL VARCHAR2(10)
SALESPERSON_ID NOT NULL VARCHAR2(10)
CUST_ID NOT NULL VARCHAR2(10)
SALE_DATE DATE
VIN NOT NULL VARCHAR2(20)
MILEAGE NUMBER(38)
VEHICLE_STATUS VARCHAR2(15)
GROSS_SALE_PRICE NOT NULL NUMBER(8,2)
DEALER_ID VARCHAR2(5)
VEHICLE_CODE VARCHAR2(10)
SQL> desc Vehicles
Name Null? Type
----------------------------------------- -------- ----------------------------
VEHICLE_CODE NOT NULL VARCHAR2(10)
DESCRIPTION VARCHAR2(100)
SQL> desc OLTP_Vehicles
Name Null? Type
----------------------------------------- -------- ----------------------------
VIN NOT NULL VARCHAR2(20)
TRADE_ID NOT NULL VARCHAR2(10)
TYPE VARCHAR2(10)
MAKE VARCHAR2(15)
MODEL VARCHAR2(15)
WHERE_FROM VARCHAR2(30)
WHOLESALE_COST NOT NULL NUMBER(8,2)
Here is a subset of the data:
SQL> SELECT * FROM Vehicles;
VEHICLE_CODE DESCRIPTION
--------------- ------------------------------
1 Ford, Fiesta
2 Chevrolet, Tahoe
3 Chevrolet, Colorado
4 Ford, Taurus
5 Ford, F-150
6 Chevrolet, Silverado
7 Ford, Focus
8 Dodge, Charger
9 Dodge, Challenger
10 Chevrolet, Suburban
11 Ford, Expedition
VEHICLE_CODE DESCRIPTION
--------------- ------------------------------
12 Ford, F-350
13 Dodge, Dakota
14 Ford, Edge
15 Dodge, Durango
16 Ford, F-250
17 Chevrolet, Corvette
18 Ford, Fusion
19 Dodge, Ram
20 Chevrolet, Impala
21 Chevrolet, Malibu
22 Chevrolet, Camaro
VEHICLE_CODE DESCRIPTION
--------------- ------------------------------
23 Chevrolet, Camaro
23 rows selected.
SQL> Select * From OLTP_Vehicles;
VIN TRADE_ID TYPE MAKE MODEL WHERE_FROM WHOLESALE_COST
-------------------- ---------- ---------- --------------- --------------- -------------------- ---------------
98765432FV87654321V0 9009009000 Truck Chevrolet Silverado Washington, DC $45,000.00
25667894FC36948523V8 9009009001 Car Ford Fusion Virginia $14,000.00
95786985HC58635889C2 9009009002 Car Chevrolet Camaro Maryland $35,000.00
95678234FV53375329V5 9009009003 Truck Dodge Ram Maryland $35,000.00
93678247HC45852485F5 9009009004 Truck Dodge Dakota Washington, DC $25,000.00
25786785HV45785547T4 9009009005 Car Ford Focus Washington, DC $15,000.00
27894578FC56778852C5 9009009006 Truck Chevrolet Silverado Virginia $9,000.00
93678574HV45886348V5 9009009007 Car Ford Fiesta Maryland $13,000.00
68975324HF45885337C6 9009009008 Car Dodge Charger Washington, DC $35,000.00
98768859HC58785672H2 9009009009 Truck Chevrolet Colorado Washington, DC $20,000.00
96587453GH45772535C1 9009009010 Truck Chevrolet Silverado Virginia $40,000.00
SQL> Select * From Sales;
SALE_ID SALESPERSO CUST_ID SALE_DATE VIN MILEAGE VEHICLE_STATUS GROSS_SALE_PRICE DEALE VEHICLE_CODE
---------- ---------- ---------- --------- -------------------- ---------- --------------- ---------------- ----- ---------------
3000001000 6000000600 1000100000 17-FEB-28 98765432FV87654321V0 0 New $55,000.00 D0001
3000001001 6000000601 1000100100 17-MAR-13 25667894FC36948523V8 25000 Used $24,000.00 D0002
3000001002 6000000602 1000100200 17-MAY-02 95786985HC58635889C2 4 New $45,000.00 D0003
3000001003 6000000603 1000100300 17-JUN-10 95678234FV53375329V5 3 New $45,000.00 D0001
3000001004 6000000604 1000100400 17-JAN-09 93678247HC45852485F5 45666 Used $35,000.00 D0002
3000001005 6000000605 1000100500 17-DEC-12 25786785HV45785547T4 0 New $25,000.00 D0003
3000001006 6000000606 1000100600 17-NOV-31 27894578FC56778852C5 174000 Used $19,000.00 D0001
3000001007 6000000607 1000100700 17-OCT-25 93678574HV45886348V5 1 New $23,000.00 D0002
3000001008 6000000608 1000100800 17-AUG-22 68975324HF45885337C6 15 New $45,000.00 D0003
3000001009 6000000609 1000100900 17-JUL-01 98768859HC58785672H2 35000 Used $30,000.00 D0001
3000001010 6000000600 1000100000 17-JUL-01 96587453GH45772535C1 3 New $50,000.00 D0002
Theoretically this can be done using concatenation.
SELECT
* --INSERT YOUR COLUMNS
FROM
Sales s
INNER JOIN
OLTP_Vehicles OV ON OV.VIN = s.VIN
INNER JOIN
Vehicles V ON CONCAT(OV.MAKE, ', ',OV.MODEL) = V.DESCRIPTIONPlease do let me know if this works.
Here is a DB fiddle of the same using PostGreSQL