title: Generic SQL Query with group by
body: <p><a href="http://www.dbasupport.com/forums/attachment.php?attachmentid=587&amp;d=1336661439" rel="nofollow"><strong>Please click here for sample tables and description</strong>.</a></p>
  
  <p>I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per attached images.</p>
  
  <p>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.</p>
  
  <p>The query should be generic so that it can be used in any database (MySQL/Oracle/MSSQL/DB2)</p>
  
  <p>Please help.</p>
  
  <pre><code>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'); 
  </code></pre>
  
  <p>Here is the query that I tried but didn't able to get desired result:</p>
  
  <pre><code>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;
  </code></pre>