How to calculate average using case when and distinct count?

Question

My source table contains sales information. Each row is a person and records every time they've shopped/where. I can therefore calculate the average transaction value per industry by the following:

select 
    industry,
    COALESCE(AVG(CASE WHEN shopcode  in (1,2,4) THEN dollar END), 0) AS avt
from sales 
group by industry

But source how can I adapt this to calculate the spend per distinct count of user i.e.: sum(dollar)/count(distinct person) so similar to above but instead of sum/count(*) sum/count(distinct person)... I need to use coalesce with this as well.

Answer 1

Hi @dimkhannaITaly ,

Welcome to Microsoft Q&A!

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

You could refer below example and check whether it is helpful to you.

create table sales  
(industry varchar(10),  
shopcode int,  
dollar int,  
person varchar(10))  
  
insert into sales values  
('AA',1,120,'Ann'),  
('AA',2,300,'Bobby'),  
('AA',2,200,'Bobby'),  
('AA',3,100,'Tom'),  
('BB',4,560,'Cathy'),  
('BB',4,230,'Cathy')  

select   
industry,  
COALESCE(AVG(CASE WHEN shopcode  in (1,2,4) THEN dollar END), 0) AS avt  
from sales   
group by industry  

select   
industry,  
COALESCE(SUM(CASE WHEN shopcode in (1,2,4) THEN dollar END), 0)/COUNT(CASE WHEN shopcode in (1,2,4) THEN person END) AS avt  
from sales   
group by industry  

Output:

industry avt  
AA 206  
BB 395  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.