Group By Query Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Group By Query Help

  1. #1
    Join Date
    May 2012
    Posts
    5

    Group By Query Help

    I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per below:
    VEHICLE_CLASS
    -------------------------------------------
    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
    ------------------------------------------
    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.


    Please see attached image to view tables.
    Please help me, how can I get result rows.
    Thank you.
    Attached Images Attached Images

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool

    Do you actually expect us to do your homework?

    If you want to learn, you should at least try to code the query on your own -- that is the purpose of homework!
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2012
    Posts
    5

    Angry This is not the homework

    I am sorry, you take it that way.

    This is not the homework.

    I changed the tables so that I can't share my company's data.

    Also Here is what I have tried in MySQL,
    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;


    But its not working.

    I need the solution in four different databases, i.e. Oracle, MySQL, MSSQL and IBM DB2.

    I know it has to do something with 'DECODE' function in Oracle but as a newbie, I am not proficient in writing such complex queries.

    You can give me more hint that will be OK. I am not asking for complete query.

  4. #4
    Join Date
    May 2012
    Posts
    5

    Need Help converting MySQL query in Oracle

    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.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by deduyay View Post
    I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per below:
    VEHICLE_CLASS
    -------------------------------------------
    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
    ------------------------------------------
    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.


    Please see attached image to view tables.
    Please help me, how can I get result rows.
    Thank you.
    Two notes.

    1- Result set is not clear; only vehicle_001 is of class ABC but three vehicles are listed in the report ?!

    2- Do not think DECODE() will cut it for you, specially if you need to write a query that is compatible with four different RDBMS engines.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width