Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2017
    Posts
    1

    Question Unanswered: Grouping results from multiple rows

    I am trying to figure out the best way to select data in to a single row oer "driver_id" and "log_date". Here is the result form a basic select:



    DRIVER_ID LOG_DATE STATUS DURATION
    ===================== ================ ====== ========

    RBROUSE 2017-03-13 1 9.02
    RBROUSE 2017-03-14 4 0.08
    RBROUSE 2017-03-14 2 0.03
    RBROUSE 2017-03-14 2 0.55
    RBROUSE 2017-03-14 3 10.45
    RBROUSE 2017-03-13 3 6.88
    RBROUSE 2017-03-13 2 1.22


    The result that I want would look like:

    DRIVER_ID LOG_DATE STATUS1DURATION STATUS2DURATION STATUS3DURATION STATUS4DURATION
    RBROUSE 2017-03-13 9.02 1.22 6.88 0.0
    RBROUSE 2017-03-14 0.0 0.58 10.45 0.08


    So basically for each driver and log date I want to sum the total "duration" for each status and return in one single row. I have fiddled with joining my table onto itself, but I just don't know if this is possible. Any feedback would be much appreciated.

    Windows 8.1
    DB2 Express version 10.5.500.107 fix pack 5

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    579
    Provided Answers: 3
    To sum something for each driver and log time, you need GROUP BY and SUM.
    To put each sum on a different column, you may use UNION.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Code:
    select DRIVER_ID, LOG_DATE
    , COALESCE(SUM(CASE STATUS WHEN 1 THEN DURATION END), 0) STATUS1DURATION
    , COALESCE(SUM(CASE STATUS WHEN 2 THEN DURATION END), 0) STATUS2DURATION
    , COALESCE(SUM(CASE STATUS WHEN 3 THEN DURATION END), 0) STATUS3DURATION
    , COALESCE(SUM(CASE STATUS WHEN 4 THEN DURATION END), 0) STATUS4DURATION
    from table(values 
      ('RBROUSE', date('2017-03-13'), 1, 9.02)
    , ('RBROUSE', date('2017-03-14'), 4, 0.08)
    , ('RBROUSE', date('2017-03-14'), 2, 0.03)
    , ('RBROUSE', date('2017-03-14'), 2, 0.55)
    , ('RBROUSE', date('2017-03-14'), 3, 10.45)
    , ('RBROUSE', date('2017-03-13'), 3, 6.88)
    , ('RBROUSE', date('2017-03-13'), 2, 1.22)
    ) t(DRIVER_ID, LOG_DATE, STATUS, DURATION)
    group by DRIVER_ID, LOG_DATE;
    Regards,
    Mark.

Posting Permissions

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