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

casegroup byquerysql-server-2016t-sql

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

Best Answer

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.

Related Solutions

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Sql-server – Sum currencies excluding duplicatied records

I agree with oNare that adding a sqlFiddle that builds a simplified version of your data model, shows what you have tried so far, and shows the desired results would be most helpful.

That said, you might want something like the following in order to avoid double-counting the sales for any customers that were given multiple offers. I've put inline comments quoting the relevant parts of your question in this example query:

SELECT s.customerId, s.totalSellValue, o.* -- "other CRMOffer data"
FROM CRMOffer o
JOIN (
    -- "I must then group by CRMOffers customerID and sum sell values, but sum only once for each SellPK"
    -- Compute total sell value for each customer before joining to CRMOffer in order to avoid double-counting
    -- NOTE: Grouping by Sell.customerId is the same as grouping by CRMOffers.customerId since the two must match
    SELECT customerId, SUM(sellValue) AS totalSellValue
    FROM Sell
    WHERE customerId IS NOT NULL -- "If Sell table doesnt have a specific customerID, sell value will be null"
    GROUP BY customerId
) s
    ON s.customerId = o.customerId

Best Answer

Related Solutions

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

Sql-server – Sum currencies excluding duplicatied records

Related Question