Get Average amount for different type

Hi, I have a scenario that need to calculate amount ..,I have one table SalesAmount.

   saleid  custid  amnt type
     100      10      5    Retail
     100      20      6    Retail
     200      10      8    wholesale
     200      30      4    Wholesale
     300      10      5    Retail
     300      20      6    WholeSale

I have to calculate the amount for a given saleid ,if the type is either "Retail" or "WholeSale" but not both for same saleid .
Example: saleid 100 type is Retail with 2 records then my saleamount is 5+6/2=5.5...for 200 8+4/2=6 bcoz type is wholesale for 2 records.
But for Saleid 300 we 2 different type,in that case my saleamt should return as Zero not average as above...

Can any one help me how to write a query to check all these

more ▼

asked Sep 24, 2012 at 10:27 AM in Default

avatar image

14 12 14 15

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

select saleid, type , case count(type) when count(type) >=2 sum(amnt)/count(type) else 0 from table group by saleid,type

more ▼

answered Sep 24, 2012 at 11:16 AM

avatar image

2.8k 56 65 71

thanks kannan for ur replay but need some more enhancements , For Ex:In some cases saleid having 1 record with either retail or wholesale need to get that amount value.

and 3 records with 2 of Retail and 1 wholesale i have to get 0 as amount(mixed scenario) but your query won`t works in that case.

Sep 24, 2012 at 11:34 AM ANJI
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 24, 2012 at 10:27 AM

Seen: 407 times

Last Updated: Sep 24, 2012 at 11:46 AM

Copyright 2018 Redgate Software. Privacy Policy