Click here to Skip to main content
13,869,106 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
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
   
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.190214.1 | Last Updated 11 Oct 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

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