SQL - Retrieve Avg Score from Group

Question

My Database contains the following:

|    ID     |     UID      |     file    |    score    |    time   | 
     1    |     a827vgj28df |  jack_123  |      75     |       12:44 
     2    |     ayeskfkfjhk  | jack_999   |      5     |      12:12 
     3     |    b83759       | adam_123   |      7      |      12:12

Goal: I am trying to get a query that displays the avg scores for each file prefix (jack/adam)

To display like:

|  Key   |   AVG 
  jack       40 
  adam        7

@Randy The thing is, I do not know how to grab the filename prefix "Jack/Adam", without grabbing the whole file name — Tyler Esposito, Mar 30 '17 at 17:03
@TylerEsposito Including at least an attempt will help us with various info like the name of your table — David Lee, Mar 30 '17 at 17:03
Most SQL dialects contain functions that allow you to locate a character or string of characters in a string, and they also typically contain a function that allow you to take the left or right portions of a string value. If you don't know about these, do a little research. Or wait long enough, and perhaps someone will answer your un-researched question. — STLDev, Mar 30 '17 at 17:05
Possible duplicate of [SQL query with avg and group by](http://stackoverflow.com/questions/10702546/sql-query-with-avg-and-group-by) — Woot4Moo, Mar 30 '17 at 17:06

score 1 · Accepted Answer · answered Mar 30 '17 at 17:04

1

You can use substring_index to extract the name prefixes. From there on, it's just a simple use of avg:

SELECT   SUBSTRING_INDEX(file, '_', 1) AS key, AVG(score)
FROM     mytable
GROUP BY SUBSTRING_INDEX(file, '_', 1)

answered Mar 30 '17 at 17:04

Mureinik

297,002
52
306
350

Works perfectly! Thank you – Tyler Esposito Mar 30 '17 at 17:09

SouthShoreAK · Answer 2 · 2017-03-30T17:05:41.970

0

Should be something like

select SUBSTRING(file, 1, 4) as Key, AVG(score)
from table
group by SUBSTRING(file, 1, 4)

You might get a better response in the future if you share what you've already tried.

edited Mar 30 '17 at 17:05

answered Mar 30 '17 at 17:04

SouthShoreAK

4,176
2
26
48

This would only work if all the prefixes are exactly four characters long. In the sample data they are ("jack", "adam"), but I don't think this is really what the OP intended. – Mureinik Mar 30 '17 at 17:05
That is absolutely true. OP didn't specify, though. – SouthShoreAK Mar 30 '17 at 17:07

score 0 · Answer 3 · answered Mar 30 '17 at 17:05

0

you can get the key using SUBSTRING_INDEX and then group by

  SELECT
      SUBSTRING_INDEX(file, '_', 1) AS key
      , avg(score) average
  from my_table  
  group by  key

answered Mar 30 '17 at 17:05

ScaisEdge

131,976
10
91
107

SQL - Retrieve Avg Score from Group

3 Answers3