I am having issue when using left join and group by simultaneously . I am having multiple rows of da-CodePudding

I am joining second table (job_timetable) with call_log table . I am having multiple rows of data for each user_id ( common field in both the tables )in job_timetable table . So I am using group by . I want to add the values of column( counter) for each set of user_id .

Table call_log

     user_id  duration 
      5019    12
      2345    23
      5019    14

Table job_timetable

     user_id  counter 
      5019    1
      5019    3
      2345    2

Expected output :

     user_id  duration average duration countersum
      5019    26          13             4

      2345    23          23             2

Current output : Returns nothing

What is wrong with my query ?

SELECT call_log.user_id,SUM(call_log.duration) as duration,avg(call_log.duration) as 
average_duration,countersum
FROM call_log 
LEFT JOIN (SELECT user_id,sum(counter) as countersum FROM job_timetable 
GROUP BY user_id )  b   on call_log.user_id= b.user_id
where call_log.user_id is not null 
group by call_log.user_id order by  call_log.user_id asc;

CodePudding user response：

You have to group by countersum too.

  create table call_log (
  user_id int(10), 
  duration int(10)      );

  insert into call_log values ( 5019,12),
                              ( 2345,23),
                              ( 5019,14);

  create table job_timetable  (
              user_id int(10), 
               counter int(10) );

  insert into job_timetable values ( 5019,1),
                                   ( 5019,3),
                                   ( 2345,2);


  SELECT call_log.user_id,SUM(call_log.duration) as duration,
                    avg(call_log.duration) as average_duration,
                                                    countersum
    FROM call_log 
    LEFT JOIN ( SELECT user_id,sum(counter) as countersum FROM job_timetable 
    GROUP BY user_id )  b   on call_log.user_id= b.user_id
    where call_log.user_id is not null 
    group by call_log.user_id,countersum 
    order by  call_log.user_id asc;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/77

CodePudding user response：

and this is from me :

I like to divide it to sub tasks like subqueries like this and join them later to be safe

 select avg_tbl.user_id tuser_id,
    avg_tbl.duration, avg_tbl.avg_duration,cnt_sum_tbl.countersum
    from 
    (select user_id, sum(duration) duration , avg(duration) avg_duration from call_log
    group by user_id ) avg_tbl
    left join 
    (select user_id, sum(counter) countersum from job_timetable
    group by user_id) cnt_sum_tbl on avg_tbl.user_id = cnt_sum_tbl.user_id

CodePudding user response：

Hmm, I did the query a bit other than you did. I've just calculated the aggregates and then joined them. At least it works with data you've provided us with

select cl.user_id, 
       sum_duration,
       avg_duration,
       sum_counter
  from (select user_id,
               sum(duration) sum_duration,
               round(avg(duration), 2) avg_duration
          from call_log
         group by user_id) cl
  join (select user_id,
               sum(counter) sum_counter
          from job_timetable
         group by user_id) jt
   on cl.user_id = jt.user_id
group by cl.user_id

db_fiddle

UPD. for upgrading readability you may use ctes instead of subqueries. (starting from MySQL 8)

with cl as (select user_id,
               sum(duration) sum_duration,
               round(avg(duration), 2) avg_duration
          from call_log
         group by user_id),
jt as (select user_id,
               sum(counter) sum_counter
           from job_timetable
          group by user_id)

select cl.user_id, 
       sum_duration,
       avg_duration,
       sum_counter
  from cl
  join jt
    on cl.user_id = jt.user_id
 group by cl.user_id

CodePudding user response：

You need to group by countersum as well

with call_log as
(select '5019' as user_id,12 as duration from dual
union all
select '2345' as user_id,23 as duration from dual
union all
select '5019' as user_id,14 as duration from dual
),
job_timetable as
(select '5019' as user_id,1 as counter from dual
union all
select '5019' as user_id,3 as counter from dual
union all
select '2345' as user_id,2 as counter from dual
)
select q1.user_id, sum(q1.duration) as total_duration,
avg(q1.duration) as average_duration, q2.counter_sum
from call_log q1 join (select user_id,sum(counter) as counter_sum from job_timetable group by user_id) q2 on (q1.user_id=q2.user_id)
group by q1.user_id, q2.counter_sum