Group by count query

[De Duyay]

Please click here for sample tables and description.
http://www.dbasupport.com/forums/attachment.php?attachmentid=587&d=1336661439


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 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');



Here is the query that I tried but didn't able to get desired result:

SELECT veh.VEHICLE_CLASS_ID, vehclass.VEHICLE_CLASS_NAME,
SUM( CASE WHEN veh.STATUS=2 THEN COUNT(veh.VEHICLE_ID) end) Completed,
SUM( CASE WHEN veh.STATUS!=2 THEN COUNT(veh.VEHICLE_ID) end) not_completed
FROM VEHICLE veh LEFT JOIN VEHICLE_CLASS vehclass
on veh.VEHICLE_CLASS_ID = vehclass.VEHICLE_CLASS_ID
GROUP BY veh.VEHICLE_CLASS_ID
having veh.VEHICLE_CLASS_ID is not null;