CODEWITHSUNDEEP
mysqlsql
HJGBAUM
HJGBAUM

Reputation: 397

SQL average of sums?

I have a database consisting of many tables, 3 tables of interest here are ThemeParks, Tickets and TicketPrices.

I currently have a query which displays the total ticket sales for each country:

SELECT SUM(TicketPrices.price) 
FROM TicketPrices, Tickets, ThemeParks
WHERE Tickets.TP_Code = ThemeParks.TP_Code
GROUP BY ThemeParks.TP_country

Query Result:

SUM(TicketPrices.price)
-----------------------
700
300
300
600

I would like to make a query that just displays the average of all of those values. How could I do this?

Upvotes: 1

Views: 169

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

The average is the sum divided by the count. So:

SELECT SUM(tp.price) / COUNT(DISTINCT tp.TP_COUNTRY)
FROM TicketPrices tp JOIN
     Tickets t
     ON ??? JOIN
     ThemeParks tp
     ON t.TP_Code = tp.TP_Code;

The problem with your query, though, is the missing ON clause. You should learn to use explicit JOIN syntax. This will help you write correct queries in the future. A simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.

Upvotes: 1

sstan
sstan

Reputation: 36473

Just wrap it inside another query:

SELECT AVG(price_sum)
  FROM (SELECT SUM(TicketPrices.price) as price_sum
          FROM TicketPrices, Tickets, ThemeParks 
         WHERE Tickets.TP_Code = ThemeParks.TP_Code 
         GROUP BY ThemeParks.TP_country) t

Also, consider switching over to ANSI join syntax.

Upvotes: 1

Related Questions