Unusual behaviour of a query when using case expression in group by, some records are being missed

Question

I have 4 table with below structure:

Table "Customer_Info"

 create table Customer_Info
(
  Customer_Number         int,
  Customer_Issued_Branch  int,
  <Other Columns related to customer>
)

Example Data:

  Insert into Customer_Info(Customer_Number,Customer_Issued_Branch)
  values (1,1001),(2,1002),(3,1003),(4,1001)

Table "Customer_Transaction_Info"

create table Customer_Transaction_Info
(
  Customer_Number    int,
  Channel_Number     int,
  Transaction_Count  int,
  <Other columns related to customer transactions >
)

Example data:

Insert into Customer_Transaction_Info(Customer_Number,Channel_Number,Transaction_Count)
Values(1,5,2),(1,6,1),(1,3,1),(1,1,1),(1,2,1),(2,5,1),(2,1,1)

Table "Customer_Channel_Info"

create table Customer_Channel_Info
(
  Customer_Number         int,
  Channel_Number          int,
  Channel_Branch_code     int
)

Example data:

insert into Customer_Channel_Info (Customer_Number,Channel_Number,Channel_Branch_code)
values (1,5,1001),(1,6,1002),(1,3,1003),(2,5,1004),(3,6,1005),(4,3,1006)

Table "Branch_Info"

create table Branch_Info
(
  Branch_Code      int,
  New_Branch-Code  int
)

Example Data :

insert into Branch_Info(Branch_Code,New_Branch-Code)
values(1001,1010),(1002,1010),(1003,1010),(1004,1010),(1005,1010),(1006,1010)

The problem is that everything seems fine until this part of my query on t4.Branch_Code = t2.Channel_Branch_code. what I mean is that the select * of below query, without group by and sum(), gives me 7 records, but as soon as I add group by part and sum, the below query returns 4 records! It's wired and I do not understand the reason! both case statements,in group part and select part of the query are exactly the same so why some record are being missed?

My Query is :

select t1.Customer_Number,
       case
         when t1.Channel_Number in (1, 2) then t3.Customer_Issued_Branch
         else t4.New_Branch-Code
       end brncode,
       sum(case
             when t1.Channel_Number = 1 then t1.Transaction_Count
             else 0
           end) Tel_Count
  from Customer_Transaction_Info t1
  left join Customer_Channel_Info t2
    on t1.Customer_Number= t2.Customer_Number
   and t1.Channel_Number = t2.Channel_Number
  left join Customer_Info t3
    on t3.Customer_Number = t1.Customer_Number
  left join Branch_Info t4
    on t4.Branch_Code = t2.Channel_Branch_code
 group by t1.customer_num,------------------------>/*This part*/
          case
            when t1.Channel_Number in (1, 2) then
             t3.cust_branch_cod
            else
             t4.New_Branch
          end

Thanks in advance

Answer 1

This behaviour is directly related to your group by - it is behaving as intended, by 'uniquing' your customer_nums (of which there are 4 in your example data).

Without further information on what you are looking to derive with this query it's hard to determine what results you wish for from this query. If you do need any further help, happy to give any guidance that I can.

See the full explanation of GROUP BY on https://www.w3schools.com/sql/sql_groupby.asp.