CODEWITHSUNDEEP
mysqlsql
Rogan Farrira
Rogan Farrira

Reputation: 15

In SQL or mySQL, how can I find a key which has the highest sum in another column while the key appears in two column?

enter image description here

Suppose I have a video chatting app that records the username of two users and the length of the call, the table is data of all the calls.

A person can appear in both user1 and user2. For example, in the table David appears in both user1 and user2. Using the data that we have on the table, how can I write a SQL query that finds the user who has the longest total call length? In this case, David has the longest total call length, which is 50 minutes.

Upvotes: 0

Views: 69

Answers (4)

jose_bacoy
jose_bacoy

Reputation: 12684

I will combine all call time for each users (user1 and user 2) then group by user. Get the top 1 record based on call time.

    Select user, sum(calltime) as calltime
    From
    (Select user1 as user, calltime
     from tbl
      Union all
     Select user2, calltime
     from tbl
     ) t
Group by user
Order by calltime desc
Limit 1;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

You can use a LEAST/GREATEST trick here:

SELECT user, SUM(length) AS total_length
FROM
(
    SELECT LEAST(User1, User2) AS user, length
    FROM yourTable
    UNION ALL
    SELECT GREATEST(User1, User2), length
    FROM yourTable
) t
GROUP BY
    user
ORDER BY
    SUM(length) DESC

Demo

Upvotes: 2

John Woo
John Woo

Reputation: 263733

Another way, which might look dirtier since mysql doesn't support any window fucntion like any other RDBMS but always give you the exact result including multiple users having the highest total length is by combining the results of both users, calculating its total length and use that value in the outer query comparing to the total sum of the same query without the use of LIMIT.

SELECT Caller, SUM(length) TotalLength
FROM
    (
        SELECT User1 AS Caller, length FROM calls UNION ALL
        SELECT User2, length FROM calls 
    ) a
GROUP BY Caller
HAVING SUM(length) = (
        SELECT MAX(TotalLength)
        FROM
        (
            SELECT Caller, SUM(length) TotalLength
            FROM
                (
                    SELECT User1 AS Caller, length FROM calls UNION ALL
                    SELECT User2, length FROM calls 
                ) a
            GROUP BY Caller
        ) a
  )

Here's a Demo.

Upvotes: 0

Fact
Fact

Reputation: 2450

with dat as 
(
Select 'Jhony' User1, 'Jennifer' User2, 23 Call_Length union all
Select 'David','Michael',10 union all
Select 'Lisa','David',40 union all
Select 'Lisa','Jennifer',5
)
Select top 1 sum(a.call_length+nvl(b.call_length,0)),a.user1,b.user2 from 
dat a
left join dat b on a.user1=b.user2
group by a.user1,b.user2
order by  sum(a.call_length+nvl(b.call_length,0)) desc

Upvotes: 0

Related Questions