Post reply

Averaging based on a condition TSQL

  • October 20, 2010 at 8:24 am

    #241895

    CREATE TABLE #TblTemp( IdINT,

    Price DECIMAL(18,9))

    INSERT INTO #TblTemp

    SELECT 1,10

    INSERT INTO #TblTemp

    SELECT 1,9999.9999

    INSERT INTO #TblTemp

    SELECT 2,15

    INSERT INTO #TblTemp

    SELECT 2,25

    SELECT Id , avg(Price) AVGPrice from #TblTemp GROUP BY Id

    Have a requirement to calculate average of a field but the average should be set to a constant when there is a specific value.

    In the above script i would want the average for Id 1 to be 1111.1111 (Constant) when there is a price value of 9999.9999.

    In other words whenever ther is a price of 9999.9999 for an id the average should be 1111.1111

    Any help is appreciated. Thanks

  • October 20, 2010 at 1:37 pm

    #1239889

    What would be your expected result if there are more than one row per id where either one row has the value 9999.9999 or all rows or sum = 9999.9999 or any other fany way?

    Your sample code covers one possible scenario. But how about the other?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • October 20, 2010 at 2:53 pm

    #1239953

    Thanks for responding.

    In the above example i want a result like this.

    Id AvgPrice

    1 1111.1111

    2 20

    If there is a record with a price of 9999.9999 for a specific id the avgprice for that id should show as 1111.1111

    There could be other prices for that Id that are not 9999.9999 but i would still want to see 1111.1111 for that id.

    If no rows has a price of 9999.9999 for an ID then price should be averaged.

  • October 20, 2010 at 3:06 pm

    #1239960

    Something along those lines?

    SELECT

    id,

    CASE

    WHEN EXISTS (SELECT 1 FROM #TblTemp t2 WHERE t1.id=t2.id AND Price=9999.9999)

    THEN 1111.1111

    ELSE AVG(Price) END AVGPrice

    FROM #TblTemp t1

    GROUP BY Id


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • October 21, 2010 at 3:08 pm

    #1240618

    Thats it! really appreicate your help on this.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply