Click here to Skip to main content
13,796,261 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have a database that I am trying to summarize below.

GAME_ID GOALIEID SAVE
2001 5 Y
2001 4 Y
2001 5 N
2001 4 Y
2001 5 N

Essentially I am trying to show by game_id and goalieid in which game did which goalie make the most saves in and how many saves did the goalie have in that game. I only put one game here to make the example very simple.

I am trying to get an sql result that can produce this

GAME_ID GOALIEID SAVES
2001 4 2

What I have tried:

SELECT GAME_ID, GOALIEID, COUNT(SAVE)
FROM GIRAFFE.MLS
WHERE SAVE = 'N'
GROUP BY GAME_ID
ORDER BY COUNT (DIDSCORE) DESC

However, this is only totaling all saves and not specific to the amount the 1 goalie made.
Posted 11-Oct-18 6:29am
Updated 11-Oct-18 8:01am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Please note, this solution is for SQL server, as I don't have MySQL, but I would expect the solutions to be similar. I had to change the name of the SAVE field to SAVEFLAG because SAVE is a reserved word in SQL server.

create table #Temp (
	GAME_ID INT,
	GOALIEID INT,
	SAVEFLAG VARCHAR(1)
	)

	INSERT INTO #Temp SELECT 2001, 5,'Y'
INSERT INTO #Temp SELECT 2001, 4,'Y'
INSERT INTO #Temp SELECT 2001, 5,'N'
INSERT INTO #Temp SELECT 2001, 4,'Y'
INSERT INTO #Temp SELECT 2001, 5,'N'

-- Goalie that allowed the most goals
SELECT top 1 GAME_ID, GOALIEID, COUNT(SAVEFLAG) 
FROM #Temp
WHERE SAVEFLAG = 'N'
GROUP BY GAME_ID, GOALIEID
ORDER BY 3 DESC

-- Goalie with the most saves
SELECT top 1 GAME_ID, GOALIEID, COUNT(SAVEFLAG) AS SAVES
FROM #Temp
WHERE SAVEFLAG = 'Y'
GROUP BY GAME_ID, GOALIEID
ORDER BY 3 DESC
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.181207.3 | Last Updated 11 Oct 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100