Post reply

Query that returns winnings for a year and zero if no winnings for year

  • June 12, 2018 at 4:56 am

    #369762


    create table #Achievements (Ach_ID INT, Description varchar (30), Bonus_Points int)
    create table #PlayersAchievments (Player_ID INT, Ach_ID INT, Winning_Date DATE)

    INSERT INTO #Achievements VALUES (10,'Gold Medal',90)
    INSERT INTO #Achievements VALUES (21,'Shield of Honour',20)
    INSERT INTO #Achievements VALUES (39,'Red plasma',75)
    INSERT INTO #Achievements VALUES (40,'Ach',75)

    INSERT INTO #PlayersAchievments VALUES (1,21,'06/15/2015')
    INSERT INTO #PlayersAchievments VALUES (2,21,'12/24/2015')
    INSERT INTO #PlayersAchievments VALUES (2,21,'11/11/2012')
    INSERT INTO #PlayersAchievments VALUES (3,39,'05/01/2013')
    INSERT INTO #PlayersAchievments VALUES (3,10,'03/02/2013')
    INSERT INTO #PlayersAchievments VALUES (3,40,'03/02/2015')

    Hi,

    Lets say I have the 2 tables above.
    I need to return all the ach_id and number of winnings for year 2015. If there are no winnings for an ach_id for the year 2015 I will return 0 if there are winnings then I will return the number of winnings for year 2015.

    Result:
    Ach_id   Winnings_2005
    10                    0
    21                    2
    39                      0
    40                   1

    Not sure how to go about that.

    Thanks

  • June 12, 2018 at 5:36 am

    #1993661

    I think the first question most will ask is, what have you tried so far to accomplish this?
    Take a look at using a GROUP BY query.

  • June 12, 2018 at 6:16 am

    #1993667

    Hi,
    Thanks for replying.
    This is what I have done. I get the results I want but it seems not the correct why of doing this.


    SELECT DISTINCT isnull (P1.ACH_ID,0) ACH_ID,isnull(CNTPLAYER,0)
    FROM #PlayersAchievments P1
    LEFT JOIN (
    SELECT Ach_ID, YEAR(WINNING_DATE)WINNING_DATE,COUNT (PLAYER_ID) CNTPLAYER
    FROM #PlayersAchievments
    WHERE YEAR(WINNING_DATE)=2015
    GROUP BY Ach_ID,YEAR(WINNING_DATE)) P2 ON P1.Ach_ID=P2.Ach_ID

  • June 12, 2018 at 7:08 am

    #1993670

    It could be simplified a bit.
    SELECT    a.Ach_ID, Winnings_2015 = COUNT(p.Player_ID)
    FROM #Achievements a
    LEFT JOIN #PlayersAchievments p ON p.Ach_ID = a.Ach_ID AND YEAR(p.Winning_Date) = 2015
    GROUP BY a.Ach_ID

  • June 12, 2018 at 7:21 am

    #1993672

    Thank you 🙂

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

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