Mysql – SELECTing an optional column, and doing SUM on it

MySQL

I have two tables (from Request Tracker 4, actually): Tickets, and Transactions.

Tickets.id == Transactions.ObjectID

but there aren't always Transactions for every Ticket.

I'm interested in the Ticket's "TimeWorked" column. This is often set on Ticket Creation, but it's also sometimes updated by a Transaction which updates "TimeWorked". I want to get the TimeWorked value as it originally was which means I think I have to backtrack through all the linked Transactions and subtract their values. Subsequently, I also want all the updated Transactions which modified that value, but I believe I've solved that problem.

Basically I want to see who set the TimeWorked value when, and what it was on ticket creation. Here's my SQL:

SELECT
Tickets.id                                                            AS Ticket,
Tickets.Subject                                                       AS Subject,
/*
SUM(Transactions.NewValue - Transactions.OldValue)                    AS TransactionTimeWorked,
Tickets.TimeWorked-SUM(Transactions.NewValue - Transactions.OldValue) AS InitialTimeWorked,
*/
Tickets.Created                                                       AS TicketCreated
FROM
    Tickets
         LEFT JOIN Transactions       ON Tickets.id=Transactions.ObjectId
            AND Transactions.Field="TimeWorked"
WHERE
    Tickets.Queue = 50
    AND Tickets.Created > "2015-06-01"
    AND Tickets.Created < "2015-09-01";

Including that first SUM line limits the number of rows returned to those Tickets which have updating Transactions. I sort of understand why, but I want all Tickets to be included, with their original create time calculated by SUMming the Transactions and subtracting, but only if they exist.

I think my WHERE clause is correct in making the JOIN values optional, but now I think I want the same on the SUM.

An example of the output I'd like:

+--------+---------+-----------------------+-------------------+---------------------+---------------------+
| Ticket | Subject | TransactionTimeWorked | InitialTimeWorked | TicketCreated       | TransactionCreated  |
+--------+---------+-----------------------+-------------------+---------------------+---------------------+
|  11234 | test01  |                  NULL |                60 | 2015-08-03 08:23:35 |                NULL |
|  11234 | test01  |                    20 |              NULL | 2015-08-03 08:23:35 | 2015-08-04 08:23:35 |
|  11234 | test01  |                    10 |              NULL | 2015-08-03 08:23:35 | 2015-08-06 15:34:35 |
|  11235 | test02  |                  NULL |                30 | 2015-07-31 12:50:37 |                NULL |
|  11236 | test03  |                  NULL |                20 | 2015-07-31 14:51:29 |                NULL |
+--------+---------+-----------------------+-------------------+---------------------+---------------------+

Best Answer

If you need only the initial and the last (current) value of TimeWorked, you only need to add GROUP BY (ticket) to your query:

-- query 1
SELECT
  ti.id                                          AS Ticket,
  ti.Subject                                     AS Subject,
  SUM(tr.NewValue - tr.OldValue)                 AS TransactionTimeWorkedTotal,
  ti.TimeWorked - SUM(tr.NewValue - tr.OldValue) AS InitialTimeWorked,
  ti.Created                                     AS TicketCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr      ON ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' 
GROUP BY
    ti.id,
    ti.Subject,
    ti.TimeWorked,
    ti.Created ;

This could also be accomplished with a different approach:

-- query 2
SELECT
  ti.id                                          AS Ticket,
  ti.Subject                                     AS Subject,
  COALESCE(ti.TimeWorked - tri.OldValue, 0)      AS TransactionTimeWorkedTotal,
  COALESCE(tr.OldValue, ti.TimeWorked)           AS InitialTimeWorked,
  ti.Created                                     AS TicketCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr
            ON  ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
            AND tr.Created = 
                ( SELECT tr1.Created               -- finds the first
                  FROM Transactions AS tr1         -- transaction (if any)
                  WHERE ti.id = tr1.ObjectId       -- for the ticket
                    AND tr1.Field = 'TimeWorked'    
                  ORDER BY tr1.Created 
                  LIMIT 1
                )
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' ;

Both the above queries will give the initial TimeWorked and the total of the transactions TimeWorked values.

If you want all the individual changes as well - and since the Transactions table has already the old and new values - it needs something different, but not more complicated than the previous queries. using query 2 for example:

-- query 2b
SELECT
  ti.id                                      AS Ticket,
  ti.Subject                                 AS Subject,
  NULL                                       AS TransactionTimeWorked,
  COALESCE(tr.OldValue, ti.TimeWorked)       AS InitialTimeWorked,
  ti.Created                                 AS TicketCreated,
  ti.Created                                 AS TransactionCreated
FROM
    Tickets AS ti
         LEFT JOIN Transactions AS tr
            ON  ti.id = tr.ObjectId
            AND tr.Field = 'TimeWorked'
            AND tr.Created = 
                ( SELECT tr1.Created               -- finds the first
                  FROM Transactions AS tr1         -- transaction (if any)
                  WHERE ti.id = tr1.ObjectId       -- for the ticket
                    AND tr1.Field = 'TimeWorked'    
                  ORDER BY tr1.Created 
                  LIMIT 1
                )
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' 

UNION ALL

SELECT
  ti.id                                      AS Ticket,
  ti.Subject                                 AS Subject,
  tr.NewValue - tr.OldValue                  AS TransactionTimeWorked,
  NULL                                       AS InitialTimeWorked,
  ti.Created                                 AS TicketCreated,
  tr.Created                                 AS TransactionCreated
FROM
    Tickets AS ti
         INNER JOIN Transactions AS tr           -- all the transactions
            ON  ti.id = tr.ObjectId             -- of a ticket
            AND tr.Field = 'TimeWorked'
WHERE
    ti.Queue = 50
    AND ti.Created > '2015-06-01'
    AND ti.Created < '2015-09-01' 

ORDER BY
    Ticket,
    TransactionCreated ;
Related Question