Search code examples
sqlfirebirdfirebird-3.0

Firebird SQL join recommendation

I am using Firebird 3.0. I have 2 tables and I want to get the following result with one query, but I couldn't do this.

USERS Table

ID USER_NAME
10 User1
20 User2
30 User3
40 User4
50 User5

CALL_HISTORY Table

ID USER_ID START_TIME CALL_DIRECTION STATUS EXTEN REMOTE_USER DURATION
1 10 09.02.2022 I 3 1007 00000000000 63
2 10 09.02.2022 I 3 1007 00000000000 37
3 10 09.02.2022 I 4 1007 00000000000 0
4 20 09.02.2022 O 3 1008 00000000000 33
5 20 09.02.2022 O 3 1008 00000000000 47
6 20 09.02.2022 O 3 1008 00000000000 10
7 30 09.02.2022 O 3 1028 00000000000 121
8 30 09.02.2022 O 3 1028 00000000000 37
9 30 09.02.2022 O 3 1028 00000000000 73
10 30 09.02.2022 I 3 1028 00000000000 61
11 40 09.02.2022 I 4 1013 00000000000 0

Expected result

USER_NAME NUM_INCOMING_CALL DURATION_INCOMING_CALL NUM_OUTGOING_CALL DURATION_OUTGOING_CALL
User1 2 100 null null
User2 null null 3 90
User3 1 61 3 231

Attempt

Here is the query I tried (and there is also a dbfiddle):

SELECT 
  outgoing_call.user_name,
  cast(incoming_call.num_incoming_call as bigint) as num_incoming_call,
  incoming_call.duration_incoming_call,
  cast(outgoing_call.num_outgoing_call as bigint) as num_outgoing_call,
  outgoing_call.duration_outgoing_call
from
  (select user_id, (select user_name from "USERS" where id=sch.user_id) user_name, count(id) num_outgoing_call, sum(duration) duration_outgoing_call
    from "CALL_HISTORY" sch
    where CALL_DIRECTION='O' AND (START_TIME BETWEEN '2022-01-01' AND '2022-03-01') AND (STATUS=3) group by user_id
  ) as outgoing_call
full join
  (select user_id, (select user_name from "USERS" where id=sch.user_id) user_name, count(id) num_incoming_call, sum(duration) duration_incoming_call
    from "CALL_HISTORY" sch
    where CALL_DIRECTION='I' AND (START_TIME BETWEEN '2022-01-01' AND '2022-03-01') AND (STATUS=3) group by user_id
  ) as incoming_call
on outgoing_call.user_name=incoming_call.user_name

The result set is not what I want. I also tried the query as "Right Join", "Left Join" and "Full Join" but, it didn't get the result I expected. Can a single query sentence be written to see the expected result?

Solution

  • Everything is much simpler than you think. All you need is a simple join and conditional aggregates like sum(case when call_direction='I' then duration end).

    Look at this: https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=1d9cbe0e617f89d8bf698b1a22d4bd76