Query in Mysql when there are two ids

Question

I have a table in Mysql that records link information from a PABX IP. It always stores the connection with a uniqueid own.

But I noticed that time in IVR is also stored in the same table with the same uniqueid.

Then when someone calls falls into the URA and is served by the person, two entries are generated in the database for the same call.

My problem is this: I need query, when I give a select * from tabela;, only display the data of the second entry with the exception of the field callduration that has to add up the value of the two entries.

I’ve been googling and I have no idea how to make a query in these patterns.

Here is a print of an example in the table:

Answer 1

Supposing:

• All records be duplicated.
• Duplicates always form a pair having a "Dial" and a "Hangup" in the column lastapp with the same uniqueid.
• All "Hangup" elements that are filled are equal to the "Dial" elements (with the exception of lastapp).

So this should work:

SELECT
    a.calldate AS calldate,
    a.clid, AS clid,
    a.src AS src,
    a.dst AS dst,
    a.dcontext AS dcontext,
    a.channel AS channel,
    a.dstchannel AS dstchannel,
    'Dial + Hangup' AS lastapp,
    a.lastdata AS lastdata,
    (a.duration + b.duration) AS duration,
    (a.billsec + b.billsec) AS billsec,
    a.disposition AS disposition,
    a.amaflags AS amaflags,
    a.accountcode AS accountcode,
    a.uniqueid AS uniqueid
FROM
    tabela a
INNER JOIN
    tabela b ON a.uniqueid = b.uniqueid
WHERE a.lastapp = 'Dial'
AND b.lastapp = 'Hangup'

If there are records that are not duplicated as well and you want to display them also along with the duplicate pair (which should be considered as a single record), try this:

SELECT
    a.calldate AS calldate,
    a.clid, AS clid,
    a.src AS src,
    a.dst AS dst,
    a.dcontext AS dcontext,
    a.channel AS channel,
    a.dstchannel AS dstchannel,
    IF(ISNULL(b.uniqueid), a.lastapp, 'Dial + Hangup') AS lastapp,
    a.lastdata AS lastdata,
    (a.duration + COALESCE(b.duration, 0)) AS duration,
    (a.billsec + COALESCE(b.billsec, 0)) AS billsec,
    a.disposition AS disposition,
    a.amaflags AS amaflags,
    a.accountcode AS accountcode,
    a.uniqueid AS uniqueid
FROM
    tabela a
LEFT OUTER JOIN
    tabela b ON a.uniqueid = b.uniqueid
WHERE a.lastapp = 'Dial'
AND (ISNULL(b.uniqueid) OR b.lastapp = 'Hangup')