SQL count() where matches a criteria-CodePudding

I have data as following. There is one order (1111) with two lines of the same ITEM (a), which are indicate by the type 'sale'.

I have the following query to sum up the amount and the tax and to calculate the quantity as following

select 
  order_num
  , item_id
  , type
  , sum(amount) over (partition by order_num, item_id) as total_amount
  , sum(tax) over (partition by order_num, item_id) as tax
  , count (*) over (partition by order_num, item_id) as qty
from scratch.saqib_ali.temp_table
qualify  row_number() over(
  partition by order_num order by order_num)=1;

This produces:

The count(*) is just counting the lines. I would like to only count the lines with type='sale' in it. How do I do that? In this case QTY should be 2.

I know I can do this using a CTE or a sub-query, but is there a more elegant way to get this?

CodePudding user response：

Can you try this one?

select 
  order_num
  , item_id
  , type
  , sum(amount) over (partition by order_num, item_id) as total_amount
  , sum(tax) over (partition by order_num, item_id) as tax
  , sum (case when type='sale' then 1 else 0 end) over (partition by order_num, item_id) as qty
from scratch.saqib_ali.temp_table
qualify  row_number() over(
  partition by order_num order by order_num)=1;

CodePudding user response：

this may work for you.

create or replace table order1 (order_num NUMBER(38,0), item_id varchar2(10), type varchar2(10), amount NUMBER(38,0), tax NUMBER(38,0));

insert into order1 values(1111,'a','sale',100,6);
insert into order1 values(1111,'a','discount',-10,0);
insert into order1 values(1111,'a','discount',-6,0);
insert into order1 values(1111,'a','sale',100,6);
insert into order1 values(1111,'a','discount',-5,0);
select * from order1;


select 
  order_num
  , item_id
  , type
  , sum(amount) over (partition by order_num, item_id) as total_amount
  , sum(tax) over (partition by order_num, item_id) as tax
  , count (*) over (partition by order_num, item_id) as qty
  , count_if (type='sale') over (partition by order_num, item_id) as qty
from order1
qualify  row_number() over(
  partition by order_num order by order_num)=1;

CodePudding user response：

An alternative to COUNT_IF is:

COUNT(CASE WHEN type='sale' THEN type END) 
     OVER (PARITION BY by order_num, item_id) AS qty

In case of type not equal 'sale' the default NULL of case expression is returned, and NULL value is not counted by COUNT function.