Query that returns winnings for a year and zero if no winnings for year
-
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 1Not sure how to go about that.
Thanks
-
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. -
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 -
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 -
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply