CODEWITHSUNDEEP
pythonmysqlsqlite
DynamicQ
DynamicQ

Reputation: 135

SQLite - Query to return related records

I have a database with the following tables with relation as mentioned. I would like to obtain the average score for each product given by every customer, as shown on the desired output.

"grp"

g_id    g_name
1       Ag.f
2       Gh.h

"customers" where c_g_id references to g_id in "grp"

c_id    c_name    g_id
"1"     "DA"      "1"
"2"     "AŠ"      "1"
"3"     "EK"      "1"
"4"     "PK"      "1"
"5"     "DD"      "2"
"6"     "AA"      "2"
"7"     "EE"      "2"

"products" where g_id references to g_id in "grp"

m_id    sequence_no  g_id   name

"1"     "1"          "1"    "product1"
"2"     "2"          "1"    "product2"
"3"     "3"          "1"    "product3"
"4"     "1"          "2"    "p1"
"5"     "2"          "2"    "p2"

"scores"

c_id references to c_id in "customers m_id references to m_id in "products"

score_id    customer_name   c_id    m_id    score

"1"         "DA"            "1"     "1"     "2" 
"2"         "DA"            "1"     "2"     "2" 
"3"         "DA"            "1"     "3"     "2" 
"4"         "AŠ"            "2"     "1"     "3" 
"5"         "AŠ"            "2"     "2"     "2" 
"6"         "AŠ"            "2"     "3"     "3" 
"7"         "EK"            "3"     "1"     "2" 
"8"         "EK"            "3"     "2"     "3" 
"9"         "EK"            "3"     "3"     "1" 
"10"        "PK"            "4"     "1"     "2" 
"11"        "PK"            "4"     "2"     "3" 
"12"        "PK"            "4"     "3"     "1" 
"13"        "DD"            "5"     "4"     "2" 
"14"        "DD"            "5"     "5"     "2" 
"15"        "AA"            "6"     "4"     "3" 
"16"        "AA"            "6"     "5"     "2" 
"17"        "EE"            "7"     "4"     "2" 
"18"        "EE"            "7"     "5"     "3"

Desired output:

sequence_no g_id    name        avg.score
1           1       "product1"  (2+3+2+2)/4
2           1       "product2"  (2+2+3+3)/4
3           1       "product3"  (2+3+1+1)/4

Upvotes: 0

Views: 95

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You coudl use avg function and group by 

select  products.name, products.g_id, avg(score)
from scores
inner join products on products.m_id = scores.m_id
group by products.name, products.g_id

eventually you can join the others table if you need some related column

Upvotes: 1

Related Questions