MySQL Groupby Query

Question

Please see link to get table in better format

In MySQL database, I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per below:

VEHICLE 
------------------------------------------- 
VEHICLE_ID  | VEHICLE_CLASS_ID  | STATUS 
------------------------------------------- 
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 
---------------------------------------------- 


VEHICLE_CLASS 
------------------------------------------ 
VEHICLE_CLASS_ID  | VEHICLE_CLASS_NAME 
----------------------------------------- 
vehicle_class_001 | ABC 
vehicle_class_002 | BCD 
vehicle_class_003 | EFG 
vehicle_class_004 | XYZ 
vehicle_class_005 | PQR 
vehicle_class_006 | STU 
---------------------------------------

There are three statuses 0 = Initiated, 1 = In Progress, 2 = Completed

I need a query to get the result rows as per below:

----------------------------------------------------------------------- 
VEHICLE_CLASS_NAME | COMPLETED_ VEHICLES | NOT_COMPLETED_ VEHICLES 
--------------------------------------------------------------------------- 
ABC                |                     |vehicle_001, vehicle_004, vehicle_006 
BCD                | vehicle_005         |vehicle_002 
EFG                | vehicle_003         |

If Status=2 then completed else it is not completed.

Here is the query that I wrote, but it doesn’t give me the correct rows.

SELECT 
      veh.VEHICLE_CLASS_ID, 
      vehclass.VEHICLE_CLASS_NAME, 
      IF(STATUS=2, GROUP_CONCAT(`VEHICLE_ID`),'') COMPLETED_ VEHICLES, 
      IF(STATUS<2,GROUP_CONCAT(`VEHICLE_ID`),'') NOT_COMPLETED_ VEHICLES 
   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;

Please help me, how can I get result rows. Thank you.

Please correct the formatting to make the post easier to read. — Matt Fenwick
– Matt Fenwick, Commented May 3, 2012 at 16:37

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

MySQL

SELECT 
  vehclass.VEHICLE_CLASS_NAME, 
  GROUP_CONCAT(CASE veh.STATUS WHEN 2 THEN `VEHICLE_ID` ELSE NULL END) COMPLETED_VEHICLES, 
  GROUP_CONCAT(CASE veh.STATUS WHEN 2 THEN NULL ELSE `VEHICLE_ID` END) NOT_COMPLETED_VEHICLES 
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;

SQLFiddle

MSSQL 2008

SELECT 
  vehclass.VEHICLE_CLASS_NAME, 
  STUFF((SELECT CASE v.STATUS WHEN 2 THEN ',' + v.VEHICLE_ID ELSE '' END
          FROM Vehicle v
          WHERE v.VEHICLE_CLASS_ID = veh.VEHICLE_CLASS_ID
          FOR XML PATH (''))
         , 1, 1, '') as COMPLETED_VEHICLES, 
  STUFF((SELECT CASE v.STATUS WHEN 2 THEN '' ELSE ',' + v.VEHICLE_ID END
          FROM Vehicle v
          WHERE v.VEHICLE_CLASS_ID = veh.VEHICLE_CLASS_ID
          FOR XML PATH (''))
         , 1, 1, '') as NOT_COMPLETED_VEHICLES
FROM VEHICLE veh 
LEFT JOIN VEHICLE_CLASS vehclass 
  ON veh.VEHICLE_CLASS_ID = vehclass.VEHICLE_CLASS_ID 
GROUP BY veh.VEHICLE_CLASS_ID, vehclass.VEHICLE_CLASS_NAME
HAVING veh.VEHICLE_CLASS_ID is not null;

SQLFiddle

ORACLE

In order to implement this in oracle, see this link for what to use in place of GROUP_CONCAT

Custom user defined aggregate function - most likely your best bet
wm_concat - undocumented, unsupported, may exist.
listagg - 11g release 2

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 3, 2012 at 16:48

Sam DeHaan

10.4k2 gold badges42 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1373028 Over a year ago

Is it possible to convert it in Oracle9i, DB2 and MSSql. Please help.

Sam DeHaan Over a year ago

@user1373028 Most of the query is relatively standard, the hard part will be converting group_concat.

Sam DeHaan Over a year ago

@user1373028 I added MSSQL implementation, comments on Oracle implementation. DB2 you're on your own, sorry. I have no experience there.

Pavel Strakhov · Accepted Answer · 2012-05-03 16:53:17Z

1

SELECT 
  vechile_class.vechile_class_name,
  GROUP_CONCAT(v1.vechile_id) AS COMPLETED_VEHICLES,
  GROUP_CONCAT(v2.vechile_id) AS NOT_COMPLETED_VEHICLES
FROM vechile_class
LEFT JOIN vechile v1 ON 
  v1.vechile_class_id = vechile_class.vechile_class_id AND v1.status = 2
LEFT JOIN vechile v2 ON 
  v2.vechile_class_id = vechile_class.vechile_class_id AND v2.status <> 2
GROUP BY vechile.vechile_class_id

answered May 3, 2012 at 16:53

Pavel Strakhov

40.8k6 gold badges93 silver badges131 bronze badges

1 Comment

user1373028 Over a year ago

Here is the MySQL query solution for above post: SELECT vc.vehicle_class_name , GROUP_CONCAT(CASE WHEN v.status = 2 THEN v.vehicle_id END) completed , GROUP_CONCAT(CASE WHEN v.status <> 2 THEN v.vehicle_id END) incomplete FROM vehicle_class vc JOIN vehicle v ON v.vehicle_class_id = vc.vehicle_class_id GROUP BY vc.vehicle_class_id; I need help converting in Oracle9i, DB2 and MSSql. Please help.

Collectives™ on Stack Overflow

MySQL Groupby Query

2 Answers 2

MySQL

MSSQL 2008

ORACLE

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

MySQL

MSSQL 2008

ORACLE

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related