Conditional Counting

• rotcha99

  Mr or Mrs. 500

  Points: 594

  More actions

  April 6, 2019 at 4:33 pm

  #2934975

  Hi,

  I am counting rows within groups to which I need to apply some rules.

  Basic rule: Every group (say customers) consists of items (orders) that are identified by a code (product) – one item’s code has changed from an old value to a new value. This means, I need to count “old” items only if there are no new ones in that group and vice versa.

  I’ve figured out a solution that seems to deliver the right results. I’m interested to hear what you think of it. Is it really the best way of doing it?

  Please find some demo code/data to show the case 😉

  drop table if exists conditionalCounting.items;

  drop schema if exists conditionalCounting;

  GO

  create schema conditionalCounting

  GO

  create table conditionalCounting.items

  (

  id int identity (1, 1) not null,

  customerId int not null,

  itemType varchar(50) not null,

  itemCode int not null,

  constraint [PK_items] primary key

  (

  [id] asc

  )

  )

  ;

  -- logic:

  -- itemCodes 10 and 100 represent the same thing.

  -- 10 was the "old" code and 100 is the "new" id.

  -- since 100 replaces 10, i do not want to count those old items if there are new ones in that group (customerId(

  -- "old" items only --> total: 2

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (100, 'count (valid, nothing new in group)', 10);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (100, 'count (valid, nothing new in group)', 10);

  -- "new" items only --> total: 2

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (200, 'count (valid, nothing old in group)', 100);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (200, 'count (valid, nothing old in group)', 100);

  -- two "old" items along with some other stuff --> total: 4

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (300, 'count (not replaced by new items in group)', 10);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (300, 'count (not replaced by new items in group)', 10);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (300, 'always count (different/additional)', 60);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (300, 'always count (different/additional)', 60);

  -- one "new" item along with some other stuff --> total: 2

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (400, 'count (valid, nothing old in group)', 100);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (400, 'always count (different/additional)', 80);

  -- two "old" items and two" new items --> total: 2

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (500, 'do not count (old/replaced)', 10);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (500, 'do not count (old/replaced)', 10);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (500, 'count (new)', 100);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (500, 'count (new)', 100);

  -- different stuff only: --> total 2

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (600, 'always count (different/additional)', 90);

  insert into conditionalCounting.items (customerId, itemType, itemCode) values (600, 'always count (different/additional)', 90);

  -- query (actual results semes okay)

  with flags as

  (

  select distinct

  customerId,

  itemCode,

  max(case when itemCode = 100 then 1 else 0 end) over(partition by customerId) as hasNewItems,

  max(case when itemCode <> 100 then 1 else 0 end) over(partition by customerId) as hasOldItems

  from conditionalCounting.items

  )

  select --distinct

  items.customerId,

  --count(case when flags.hasOldItems = 1 and flags.hasNewItems = 1 then -1 else 1 end) over(partition by items.customerId)

  count(case when flags.hasOldItems = 1 and flags.hasNewItems = 1 then -1 else 1 end)

  from conditionalCounting.items

  join flags

  on flags.customerId = items.customerId

  and flags.itemCode = items.itemCode

  group by

  items.customerId