CODEWITHSUNDEEP
sqloracle-databasejoincountsubquery
Emma Vaze
Emma Vaze

Reputation: 19

Subquery and joins SQL

I want to find out which manufacturer's vehicles are involved in the highest number of sales which incur 2 or more product points. And show the manufacturer name and the total number of sales that the manufacturer’s vehicles are involved in. 

CREATE TABLE product (
    prod_code          NUMBER(3) NOT NULL,
    prod_description   VARCHAR2(75) NOT NULL,
    prod_points        NUMBER(2) NOT NULL
);

CREATE TABLE product_sale (
        sale_no         NUMBER(8) NOT NULL,
        sale_datetime   DATE NOT NULL,
        sale_location   VARCHAR2(100) NOT NULL,
        prod_code       NUMBER(3) NOT NULL,
        officer_id     NUMBER(8) NOT NULL,
        lic_no   CHAR(10) NOT NULL,
        veh_no  CHAR(17) NOT NULL
    );

CREATE TABLE vehicle (
    veh_no         CHAR(17) NOT NULL,
    veh_manufyr     DATE NOT NULL,
    veh_maincolor   VARCHAR2(20) NOT NULL,
    veh_manufname   VARCHAR2(30) NOT NULL,
    veh_modname     VARCHAR2(20) NOT NULL,
    veh_type        CHAR(2) NOT NULL
);

enter image description here

enter image description here

I'm not sure about how to correlate 3 different joins and find the output.

Expected Output:

enter image description here

Upvotes: 0

Views: 70

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Something like that ? (not tested)

SELECT veh_manufname, total_sales_count
FROM (
   SELECT v.veh_manufname, 
          count(*) as total_sales_count,
          count( CASE WHEN prod_code IN (
                     SELECT prod_code FROM product WHERE prod_points >= 2
                ) THEN 1 END ) as sales_point_2
   FROM product_sale p
   JOIN vehicle v ON v.veh_no = p.veh_no
   GROUP BY v.veh_manufname
)
ORDER BY sales_point_2 DESC
LIMIT 1
-- LIMIT 1 works on most databases: MySql, PostgreSql, SQLite ....
-- If you are using ORACLE then remove the above line and uncomment the below one
-- FETCH 1 ROWS ONLY

-- If you are using SQL Server then (probably) SELECT TOP 1 will work but I'am not sure

Upvotes: 1

Related Questions