CODEWITHSUNDEEP
mysqlsql
Prometheus
Prometheus

Reputation: 889

SQL - Return average age depending on visits?

I'm trying to figure out this task, but after trying and trying i am not able to solve it. Can someone with more SQL experience help me out?

I have 3 tables:

enter image description here

[enter image description here

enter image description here

The task is to calculate the average age (alter) from each person who visited a restaurant located in Salzburg. If a person visited a restaurant that is located in Salzburg twice, it should be added to the average age (Emily in this case).

Since Piccolo and Stella are located in Salzburg, 4 visits happen overall.

But how do i pull this off in SQL?

How do i count the visits?

Any help is appreciated. Thanks in advance everyone!

MySQL Version: 8.0.20 Using MySQL Workbench.

EDIT 4:

Was able to find the solution on my own more or less, although the answer from @Nick is awesome as well, which is why i accept his answer as correct. Thank you all so much and @Nick! Just in case someone runs into the same issue, my final solution is:

SELECT (AVG(age)) FROM PERSON INNER JOIN ISST ON PERSON.person_name = ISST.person_name INNER JOIN PIZZERIA ON ISST.pizzeria_name = PIZZERIA.pizzeria_name && PIZZERIA.stadt="Salzburg";

Upvotes: 0

Views: 143

Answers (1)

Nick
Nick

Reputation: 147156

Since you want to calculate the average age of all visitors (regardless of whether a person visits a pizzeria in the same city twice), you can just JOIN your visits table to the pizzeria and person tables and average the results:

SELECT AVG(p.alter)
FROM ISST i
JOIN PIZZERIA z ON z.pizzeria_name = i.pizzeria_name
JOIN PERSON p ON p.person_name = i.person_name
WHERE z.stadt = 'Salzburg'

Output:

25.75

Demo on dbfiddle

Upvotes: 1

Related Questions