finding a player's most frequent position

DU_fall_analytics_baseball_player_most_frequent_position.sql

Nate, I am working on finding the primary position as we talked about.  I have queried the db and summed each of the positions for a given player, but have hit a roadblock with finding the max and making that the primary position.  I thought of using a CASE statement, but I would have to have a variable for the current max that updates as it looks at each position.  I can do that in python, but I am not sure how to do that in sql.  Thanks.  I will paste my code below.
SELECT
    playerID,
    sum(G_all) Total_Games,
    sum(GS) as Games_Started,
    sum(G_p) as Games_Pitched,
    sum(G_c) as Games_Catcher,
    sum(G_1b) as Games_First_Base,
    sum(G_2b) as Games_Second_Base,
    sum(G_3b) as Games_Third_Base,
    sum(G_ss) as Games_Short_Stop,
    sum(G_lf) as Games_Left_Field,
    sum(G_rf) as Games_Right_Field,
    sum(G_cf) as Games_Center_Field,
    sum(G_of) as Games_Out_Field,
    sum(G_dh) as Games_DH
    

SELECT 
	-- 3) Finally we grab "the first row" from a group; 
	-- https://stackoverflow.com/a/13327987/1175496
	DISTINCT ON ("playerID")
	"playerID", 
	"position", 
	"sumPositionValue"
FROM (
	-- 2) Then we do the aggregation
	SELECT 
		"playerID",
		"position",
		SUM("positionValue") as "sumPositionValue"
	FROM  (
		-- 1) First we unpivot
		-- https://stackoverflow.com/a/10625294/1175496
		SELECT																					
			"playerID",	
		    -- finish these -------v-----------------------------vvv
		   unnest(array['GS', 'G_p', 'G_c', 'G_1b', 'G_2b', 'G_3b']) AS "position",				
		   unnest(array["GS", "G_p", "G_c", "G_1b", "G_2b", "G_3b"]) AS "positionValue"			
		FROM "Appearances"	
		-- end unpivot
	) as "playerUnipivot"
	GROUP BY 
		"playerID", 
		"position"
) as "playerByPosition"
ORDER  BY 
	"playerID", 
	-- 3) Ordering by sumPositionValue DESC, ensures the "first" row for a playerID, will be the row with the highest "sumPositionValue"
	"sumPositionValue" DESC