Group by count query

[deduyay]

Please see attached image for sample tables and description.

I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per attached images.

I need a query to get the result rows with how many VEHICLES each VEHICLE_CLASS has grouped by status as shown in attached image.

The query, I need should be generic so that it can be used in any database (MySQL/Oracle/MSSQL/DB2)

Please help.

CREATE TABLE VEHICLE
(
VEHICLE_ID varchar(20),
VEHICLE_CLASS_ID varchar(30),
STATUS int
);

CREATE TABLE VEHICLE_CLASS
(
VEHICLE_CLASS_ID varchar(30),
VEHICLE_CLASS_NAME varchar(30)
);

INSERT INTO VEHICLE
(VEHICLE_ID, VEHICLE_CLASS_ID, STATUS)
VALUES
('vehicle_001', 'vehicle_class_001', 0),
('vehicle_002', 'vehicle_class_002', 1),
('vehicle_003', 'vehicle_class_003', 2),
('vehicle_004', 'vehicle_class_001', 0),
('vehicle_005', 'vehicle_class_002', 2),
('vehicle_006', 'vehicle_class_001', 0),
('vehicle_007', NULL, 1);

INSERT INTO VEHICLE_CLASS
(VEHICLE_CLASS_ID, VEHICLE_CLASS_NAME)
VALUES
('vehicle_class_001', 'ABC'),
('vehicle_class_002', 'BCD'),
('vehicle_class_003', 'EFG');