x

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

ANJI gravatar image

ANJI
14 9 14 14

(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

aRookieBIdev gravatar image

aRookieBIdev
2.3k 47 55 61

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1944

asked: Sep 24, 2012 at 10:27 AM

Seen: 320 times

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