Get Average amount for different type

Question

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

Answer 1

0

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

answered Sep 24 '12 at 11:16 AM

aRookieBIdev
2k ● 24 ● 39 ● 46

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 '12 at 11:34 AM ANJI

(comments are locked)

Get Average amount for different type

Follow this question

Related Questions