CODEWITHSUNDEEP
sqlpostgresqlcountnull
Sheldon
Sheldon

Reputation: 179

How to include zeros and null in the aggregation COUNT with some constraint

enter image description here

I have the below table, I have to count the number of quant that is between before_avg and after_avg in a cust, prod, month combination. I have to make sure the count for month January and December is null because there is no month before January and there is no month after December, and you cannot check if a value is between a null and an int. And I also need to make sure for rest of the month, if there is no quantity between before_avg and after_avg, I need to display 0. I tried simple approach like using between clause. This just filtered out any count that is either 0 or null. I also tried to separate out month January and December, and try to make the count showing as zero. I actually look up on Stack Overflow, people say we can do left join or right join to make it happen. I tried and since I have three keys in group by, it didn't seem to work very well.

I know a lot of you guys may suggest functions like nvl to do the job, but unfortunately, I am only allow to use standard SQL syntax.

Therefore, I am asking for advices and suggestions on how to approach this? I can write queries, but I need some ideas and thoughts from you guys!

Thank you very much!!

Below is some sample code I created for you guys to try: (This is in PostgreSQL)

create table abc (
    cust varchar(20), 
    prod varchar(20),
    month integer,
    quant integer,
    before_avg integer, 
    after_avg integer) 
    
insert into abc values ('Boo', 'Apple', 1, 399, null, 461); 
insert into abc values ('Boo', 'Apple', 1, 650, null, 461); 
insert into abc values ('Boo', 'Apple', 4,620, 400,303); 
insert into abc values ('Boo', 'Apple', 4,870, 400,303); 
insert into abc values ('Boo', 'Apple', 12,575,482,null);
insert into abc values ('Boo', 'Apple', 12,670,482, null); 

insert into abc values ('Chad', 'Banana', 1, 800, null, 461); 
insert into abc values ('Chad', 'Banana', 1, 445, null, 461); 
insert into abc values ('Chad', 'Banana', 4,456, 400,303); 
insert into abc values ('Chad', 'Banana', 4,237, 400,303); 
insert into abc values ('Chad', 'Banana', 12,523,482,null); 
insert into abc values ('Chad', 'Banana', 12,584,482, null);

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I have the below table, I have to count the number of quant that is between before_avg and after_avg in a cust, prod, month

This sounds like aggregation:

select cust, prod, month,
       count(*) filter (where quant between before_avg and after_avg)
from abc
group by cust, prod, month;

You can write this without filter (which seems silly, given that filter is ISO SQL standard syntax):

select cust, prod, month,
       sum( (quant between before_avg and after_avg)::int )
from abc
group by cust, prod, month;

Upvotes: 1

Related Questions