0

I have a set of Hockey stats imported into a data frame in R. I'm having trouble finding the right queries for the set of data I want.

All records are kept in 1 table called skaters

Name     Team     Opp     G
AAAAA     PHI     BOS     2
BBBBB     NYR     OTT     7
AAAAA     PHI     BOS     9
DDDDD     BOS     PHI     3
EEEEE     BOS     PHI     1
FFFFF     PHI     BOS     2
GGGGG     OTT     NYR     3

I'd like to find a way to take a sum of G per team. Thoughts were to you use a query like

SELECT DISTINCT(Team), SUM(G) FROM skaters but this didn't give me what I was anticipating.

Then I found the GROUP BY function, which found the correct data. Then I wanted to up the ante once more.

I want to perform a query that will perform the sum I was looking for, but for both the home and away teams and compare them.

rawr
  • 20,481
  • 4
  • 44
  • 78
Neurax
  • 3,657
  • 2
  • 13
  • 18
  • 1
    Hint: This is a basic `group by` query. – Gordon Linoff Nov 25 '14 at 04:14
  • if you're using r, why would you want to mix in sql? [here](http://stackoverflow.com/questions/16657512/apply-function-conditionally/16657546#16657546) are a bunch of approaches to these types of problems – rawr Nov 25 '14 at 04:45
  • @rawr I don't have the requirement of using SQL, but as I am already unfamiliar with R, it seemed like a good idea to use a SQL-like querying environment. – Neurax Nov 25 '14 at 05:28

4 Answers4

2

Hope this what you are looking for.

SELECT Team, SUM(G) 'Sum' FROM skaters group by Team
alj
  • 170
  • 10
1
library(data.table)
setDT(df)  # convert data frame to data table
df[,list(sum_G = sum(G)), by=Team]  
#    Team sum_G
# 1:  PHI    13
# 2:  NYR    10
# 3:  BOS     4

# Or 

library(dplyr)
df %>% group_by(Team) %>% summarise(sum_G = sum(G))
KFB
  • 3,501
  • 3
  • 15
  • 18
0

Since you state that you have the stats in a data frame:

aggregate(skaters$G, list(skaters$Team), sum)
goangit
  • 451
  • 4
  • 6
0
    Select Team ,Sum(G) as Total from skaters Group by Team
Dhaval
  • 2,341
  • 1
  • 13
  • 16