question

dimkhannaITaly-0725 avatar image
0 Votes"
dimkhannaITaly-0725 asked MelissaMa-msft commented

How to calculate average using case when and distinct count?

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.


sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

For this type of question, we recommend that you post CREATE TABLE statement for your table(s), together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to know the expected results given the sample. Finally, we need a short description of the business rules which explains why you want that answer.

This makes it easy for us to copy and paste into a query window to develop a tested solution. It also helps to clarify what you are asking for.

As it stands, I don't even want to try a guess of what you may be looking for.

The word "source" has a link to https://www.olehanakorea.com/. Is this link related to the problem? In such case how? (No, I did not go there to check.)

0 Votes 0 ·

Hi @dimkhannaITaly-0725,

Could you please provide any update?

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @dimkhannaITaly-0725,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.